topic Re: Calculate Dimension value for a max / min Measure in QlikView

Calculate Dimension value for a max / min Measure

maschiliro — Thu, 11 Oct 2018 21:13:51 GMT

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

I have the following Dimensions:

and Measures:

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 — Fri, 12 Oct 2018 05:57:32 GMT

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 — Fri, 12 Oct 2018 08:57:04 GMT

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: