I am having trouble displaying some data in BEx analyzer.
Data in cube is like this:Location Month Active Population A 2014001 X 50
A2014002 50 A 2014003 50 B 2014001 X 50 B 2014002 50 B 2014003 50 C 2014001 X 100 C 2014002 X 100 C 2014003 X 100
And I want to display like this (my problem is with year to date)
Because I want that if the location was active at least one month of YTD interval, it should sum to total population. Still, if it was active more than one period, I just want to count this population once to the total sum.Month (2014003) YTD (2014001-2014003) Population 100 200
In the query I have filter ACTIVE = X.The issue is that I tried making a calculated kf with agregation reference average and characterisitc reference Month, but the result is not what is expected. It does: (50+50+100+100+100)/3 = 133.33
This is wrong because I want it to consider 100 only time.
If I make a calculated key figure based on population with agregation reference last value and characterisitc reference Month. But then, it only displays 100, as it is the only active population in 2014003.
I can't display Location on the table, otherwise it would be easy. i should display only the total value.
Is there a way to display the information the way I want (just using query designer)? Or do I need to add a KF on the cube like YTD and fill this value the way I want?
Thanks in advance for your help,