Mix of averages and sums as consolidations

[MathiasBeckers]

Hi,

I'm faced with following issue and I was wondering if anybody has tried the same thing:

I'm designing a cube that holds information about the number of employees per country, per month.
In order to get meaningful figures I would like to have two ways of aggregating values:

1) On the full year consolidation I would like an average of all underlying months.
2) On the country consolidation I would like to have the (normal) sum of all underlying departments that make up a country.

So in other words, if the country UK consists of 5 departments with each 10 employees each month I would like to see following results:
- DPT 1 / JAN = 10
- DPT 1 / FULL YEAR = 10
- UK / JAN = 50
- UK / FULL YEAR = 50

Is there a way of achieving two ways of consolidating elements without having to write a rule for each combination that needs to be an average?

Thanks a lot,
Mathias.

[tomok]

Is there a way of achieving two ways of consolidating elements without having to write a rule for each combination that needs to be an average?

Firstly, let's make it clear that requirement #1 is not a consolidation so it will have to be calculated via a rule. Requirement #2 IS a consolidation so no work is required as long as you have the time period hierarchy done correctly. The best answer, IMO, is going to be to add a Measures dimension, if you don't already have it and have two elements, Count and Average. Count will be where you store the headcount numbers. Average will be a rule calculated amount, something like this:

['Year','Average']=IF(ELLEV('Country',!Country)<>0,DB(CubeName,!Country,!Period,'Count')\ELCOMPN('Country',!Country);

If these are your only dimensions you can skip the ELLEV check and just put a C: in front of the right side of the rule. If you do that it will only apply to consolidated elements so if you have another dimension in the cube the average will only work on consolidated nodes in that dimension too. The rule above will calculate the average on all nodes except only "Year' in the Period dimension and only parents in the Country dimension.

[MathiasBeckers]

Thanks for your help! I just have an additional question to your solution:

The ELCOMPN will count all elements in the consolidation but we would like to obtain an average of the non-zero elements only. To make it more clear, we've currently got a headcount cube with a time dimension (months > quarter > full year), a cost center dimension (cost center > department > division) and a country dimension (country > continent).

All levels of consolidation are standard (department is sum of underlying cost centers, continent is sum of underlying countries, ...) except the time dimension, where quarter and full year should be an average of the months that are non-zero. So for instance, if I have a new cost center with 10 FTE's starting in July, I want a Full year average of 10. With the ELCOMPN he will take the zeros in Jan-Jun also into account, which brings the average on 5.

So, what I would like to achieve if following rule: ['Value']= C: IF(ELLEV('Time',!Time) <> 0, ['Value_Hold'] \ ['Value_Count'], STET); but I'm struggling with the definition of the Value_Count measure. I can't put ['Value_Count'] = N: IF(['Value'] <> 0, 1, 0); because then he will count from the lowest level up and you would get a figure of like 27 in the Full Year cell (where I would like to see 6).

Can you do a count that only applies to the consolidated time elements and only counts the number of non-zero months?

Thanks for your help!
Mathias.