Skip to Content
0
Former Member
Jul 04, 2014 at 03:36 PM

Display value according to characteristic and last record

22 Views

Dear experts,

I am having trouble displaying some data in BEx analyzer.

Data in cube is like this:

Location Month Active Population A 2014001 X 50

A

2014002 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,

Kind regards,

André Oliveira