Is there a simple way to get a consolidated average instead of a sum?
If your dimension is static i.e. will not change you could just change the weightings against the elements i.e. if you have a consolidation with 10 children make the weighting of each child equal to .1.
If the dimension is not static then you will need to use rules. There are a couple of ways you can do this.
The first is to sum up all of the children and divide by the count to get an average. The downside of this rule is you have to update it if your dimension structure changes. i.e.
The second method I have used is to create a dummy consolidation that mirrors your primary consolidation. Make the rule something like this:
The disadvantage of this approach is you have to keep two consolidations in sync.
There are posibly ways of lso doing this via an ETL process but my experience with ETL does not stretch to these types of calculations.
Hope this helps.
Another way is to have 3 elements
1. The basic element with normal consolidation (.e.g. Sales)
2. A counter which has a rule saying counter = n:1
3. A third element Average Sales with a rule saying Average Sales = Sales / Counter
It's a bit "wasteful" as it requires thte extra element but it's otherwise quire simple and reliable and is totaly dynamic.Best wishes
The Planning Factory, Lytham, UK
thank guys! i think i got it now
I tried all this above ways and and until now has been dificult to calculate average on nodes because we have to do it from down nodes to up nodes.
With the first method we have diferent result then the second method you mentioned.
I need get your first results not the second for me the second not are acurate, but for dinamically number of nodes and childrens!!
Do you have an idea??
If the dimension is set up correctly both rules will return the same value.
Expanding my examples further the dimension would have the following Consolidations and base level elements.
Hope this helps.
unfortunately the average(x,y) is buggy this time ->