Calculate Dimension value for a max / min Measure

[maschiliro]

Hi, my problem is to calculate a kpi in the following context.

I have the following Dimensions:

• Year
• Area

and Measures:

• Citizens (global number of citizens)
• Older people (number of citizens > 80)

I’m interested in Indicator = Older people / Citizens.

Data Set sample is as follows:

How to calculate the Kpi corresponding to the Year in wich Indicator is max o min?

Es: in the following tab the KPI is: 2001

And what about if I need to calculate the Year in wich the Indicator is ≃ 2 (between 1.8 and 2.2)?

For the case above  I’ll have Year = 2003,2007,2008. I choose min -> 2003.

[lironbaram]

hi

for getting the year with max value you can use this expression

FirstSortedValue(Year,-aggr( sum([Citizens])/Sum([Older people]),Year))

for finding the minimum year between the set of values you can use this expressions

min(aggr(if(sum([Citizens])/Sum([Older people])>=1.8 and sum([Citizens])/Sum([Older people])<=2.2,Year),Year))

[maschiliro]

Thanks Liron,

your answer is very useful.

For the second part of the question I tried your suggestion:

*min(aggr(if(sum()/Sum()>=1.8 and

sum()/Sum()<=2.2,Year),Year))*

I think that the "aggr function with if inside" has a wrong syntax, and

doesn't work.

Could you help me, please?

Il giorno ven 12 ott 2018 alle ore 07:57 Liron Baram <

qcwebmaster@qlikview.com> ha scritto: