Select all Child-Elements / Children for an Average (weighted)

      Select all Child-Elements / Children for an Average (weighted)

      Hi.

      I am looking for a way to calculate an avarage of the children of consolidated elements.

      There are Measures that hold values as percentages. When it comes to consolidation, those values are summed up. This is obviously wrong because then you may get values like 355% for the consolidated elements.
      To get it more specific: There are for example percentages for AT, CH and DE that are summed up in the parent DACH.

      How do I get an average for those parent-elements?
      In addition those averages have to be weighted, but this would be the next step.

      Thanks in advance for any advices!

      -------
      I am using Jedox 6. Maybe there is already a native feature or functionality in newer jedox-versions? If that is the case I would think of postponing this topic until we updated our version.

      Post was edited 1 time, last by “Pascal456” ().

      OK.
      I did not know about those semi-additive aggregations (jedox 6).

      But in my case it was a little bit different than in the description:
      • as described, I had to set the option 'Use for semi-additive measure declaration' for the Meaure dimension
      • different from description, I did not have to define a time dimension with the option 'Use for semi-additive aggregations'. In my case it was the country dimension.
      This is a solution that is ok for the first moment, but does not make it possible to achieve a weighted average.

      New

      I still have not found an answer to my original question.

      I will try to ask in another way:

      There is a consolidated element, e.g. DACH. It contains DE, AT and CH. Is it possible to define a rule that calculates a mean based on the underlying elements? I know that there is the possibility to do this via 'Semi-Additive Measures', but this will calculate a normal arithmetic average. I want a weighted one. Therefore I need to adress all the child elements of a certain consolidated element.

      Once again in other words:
      I want a rule that applies to all elements whose parent is called DACH. So I want to make a 'consolidation'-rule that is generic and applies to all of one parents childs. This is the better way to hard-code the names of the childs, because this would mean, that the rule has to be changed when the hierarchy is changed (or for e.g. if I want to add PT to EMEA at a later point of time).

      Has somebody an idea to achieve that? I tried to use combinations of the available rules (e.g. those functions like PALO.EISCHILD) but did not figure out a way that works perfectly fine.

      New

      I would love to see a solution on this as well.
      A similar problem exists when you have for example FTE. It should calculate a sum for the full year but an average for the months.

      @Pascal456,
      I haven't really found a proper solution. But maybe it gives you an idea.
      If the consolidated element has only base elements as children the following rule would work

      Source Code

      1. ['Dimension':'DACH']=['Dimension':'DACH'] / PALO.ECHILDCOUNT("Database","Dimension","DACH")


      As I mentioned before, there is a drawback with this formula, as you cannot tell it to include all base elements underneath.
      e.g. If DACH has the children DE, AT & CH and all 3 are base elements, all is fine. If they are consolidated elements itself, which I guess is the case, it takes the average of the three elements. This means that you would have to go down to the lowes consolidation and start there. This would make it quite cumbersome to set up all rules. In addition you would have an average of an average if e.g. DE, AT & CH are already averages themselves.
      For the example with the FTE i have an average of the months for each quarter and then the year with the average of the quarters.


      Unfortunately there is no such function as EBASECOUNT.
      And just the AVERAGE function on the consolidated element with B: activated does not work either.

      If you find a solution, please do post it.