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


    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.…816ff27759797d5d8db18684e

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

    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.…816ff27759797d5d8db18684e

    Does someone already have an idea about a possible solution?

    • 1.JPG

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

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

      64 kB, 948×375, viewed 446 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:…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.

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