# How to select automatically all elements of a subset

Hi

I am using Jedox for about a month and this is my first topic on this forum.

I would like to create a data function that selects all elements of a subset.

For example, I am taking the cube Biker from the demo of Jedox 7.
1. I pasted a view by selecting the subset Elements on level 3 by the Customers dimension.

2. The view shows all third level elements of the Customers dimension.

3. Now, I would like to create a data function in order to get the total Units of the subset Elements on level 3 of the Customers dimension.

My data function is:

### Source Code

1. PALO.DATA("localhost/Biker";"Orders";"All Orders";"All Years";"Year";"All Products";C5;"All Channels";"Variance";"Units")

The cell C5 contains the subset Elements on level 3 of the Customers dimension. The subset formula is the following:

### Source Code

1. PALO.SUBSET("localhost/Biker";"Customers";1;;PALO.HFILTER(;0;FALSO;;;;;3;3;FALSO);;;;;PALO.SORT(0;0;;0;;0;1))

The result is that the cell contains only the Units value of the first subset element, while I would like to have the total Units of all subset elements.

Does someone already have an idea about a possible solution?

Thanks
Lino

Post was edited 2 times, last by “Lino83” ().

I think, there is currently no other way than list all elements using your SUBSET-Formula and the excel-matrix-formula to get the elements on level 3 "dynamically". Next thing would be to take the SUM() of all these elements using your PALO.DATA-formula.

There already is a feature requests to your question:
feedback.jedox.com/forums/2204…of-a-subset-via-palo-data

So basically this task will always be somehow manually (maybe collecting the elements matching your criteria in a separte hidden sheet and only take the sum to visible sheets).
Please correct me, if there already is a solution to Lino's request.

Regards
Hi p.hildebrandt

Thanks for your help.

### p.hildebrandt wrote:

There already is a feature requests to your question:
feedback.jedox.com/forums/2204…of-a-subset-via-palo-data

Effectively, this is the feature that I need.

Regards
Lino
Sorry for the late response to this question - there is a solution for this.

1. Make your PALO.DATA() a PALO.DATAV() formula

2. Wrap a SUM() formula around the outside.

3. Substitute C5 for the Subset formula

=SUM(PALO.DATAV("localhost/Biker","Orders","All Orders","All Years","Year","All Products",PALO.SUBSET("localhost/Biker","Customers",1,,PALO.HFILTER(,0,FALSE,,,,,3,3,FALSE),,,,,PALO.SORT(0,0,,0,,0,1)),"All Channels","Variance","Units"))