Rule for HeadCount

[mukeshrajdass]

I'll try to make this simple..

Cube - staff

Dimension -
Positions
Dept
CostCentre
Measure

Measure Elements-
FTE (Full Time Equivalent)
HeadCount
Salary etc

I need a rule which will calculate HeadCount to be 1 irregardless of a staff being 0.5 on FTE between 2 departments. That is easy. But on the consolidated level...That staff is being shown as 2 when i need it to be shown as 1.In summary, i need something that counts the number of 0 level occurrence of a particular dimension in a given view. Almost like DIMSIZ not for the whole dimension but for what is in a particular view.

['HeadCount']=N:(if['FTE']>0,1,0);<---But on C: Level there is double counting...

Any ideas ?

[EvgenyT]

Use ConsolidatedCount function

Regards

[mukeshrajdass]

Was looking at that and ConsolidatedCountUnique ...

Never used it before...documentation is not that clear...will have a go...

[mukeshrajdass]

Thanks it worked !!!

['HeadCount']=N:(if['FTE'] >0,1,0));C:ConsolidatedCountUnique(0,'position dimension','',elem1-n,'HeadCount');

Appreciate your pointer....

[EvgenyT]

Good to hear you got it working. You are welcome.