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

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

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


      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.

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

      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.
    • As your suggestions would produce a simple average of the childs (which could simply be done with semi additive measures) this does not touch my problem:

      I am looking for a way to adress all the children of a certain consolidated element.

      One more time another attempt to describe my problem: I will not call it average now, because I think this is what this discussion led into a wrong direction. Is it possible to do an action on all the children of a certain consolidated element without knowing which element names those children have or how many they are?

      I know I want to consolidate and make calculations with the children of the element EMEA. I can now calculate a weighted average like this:

      Source Code

      1. ['EMEA'] = (['Net Sales','DACH'] * ['Quantity','DACH'] + ['Net Sales','BeNeLux'] * ['Quantity','BeNeLux']) / sum(['Quantity','DACH'],['Quantity','BeNeLux'])

      But how do i match the case when there will be a new child element for 'EMEA'. With my current approach I have to add change the existing rule. (Hence this is on Region-Level, I would also have to add a new rule for the new child of EMEA [the new area e.g. CEE] as well)
    • Hi,
      have you looked into rule templates? They allow you to parametrize rules, thus saving you some of the work a hardcoded list would cause (espcially if you have many rules). For example, you can create rule templates based on the result of a stored subset (e.g. list of all children of an element).
      You would need to update the rule template instances when the dimension changes, but if this happens e.g. via integrator job, you can do it from within the job itself with a "JedoxDatabase" load.