How to select automatically all elements of a subset

    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
    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"))