How to select automatically all elements of a subset

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • 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.

      forum.jedox.com/index.php/Atta…149d712148db2770b21ad2254

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

      forum.jedox.com/index.php/Atta…149d712148db2770b21ad2254

    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.

      forum.jedox.com/index.php/Atta…149d712148db2770b21ad2254

    Does someone already have an idea about a possible solution?

    Thanks
    Lino
    Images
    • 1.JPG

      45.26 kB, 562×527, viewed 344 times
    • 2.JPG

      68.87 kB, 827×533, viewed 326 times
    • 3.JPG

      64 kB, 948×375, viewed 452 times

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