topic Re: Problem with key performance indicator calculation in QlikView

Problem with key performance indicator calculation

Thu, 07 May 2015 13:01:11 GMT

Hi all!

I have a table in the database with key performance indicators (KPIs), in Qlikview i have a report with pivot table where i sum the values of the KPIs. My problem is that i have to add a new KPIs that is the value between divide others two KPIs.

I'm trying with the combination of the functions pick and match, but i can´t do reference to others kpis where my expresion detected this complex KPI.

=pick(match(KPI_ID,'LF')+1,

Sum(VALUE)

,Sum({<KPI_ID={$(='PKO')}>} VALUE) / Sum({<KPI_ID={$(='ASI')}>} VALUE)

)

I have the kpi calculated into the data base, but really a kpi that is the operation (division) of other two kpis i cannot add the total values, because the total will be wrong, the total value will be the division.

(See attachment for more information.)

Any idea? how can i focus the problem?

Thanks for all!!

Best regards.

Re: Problem with key performance indicator calculation

sunny_talwar — Thu, 07 May 2015 13:51:26 GMT

Try this expression:

=If(Match(KPI_ID, 'ASI', 'PKO', 'RTE'),

Sum(VALUE),

Num(Sum(TOTAL <YEARMONTH> Aggr(Sum(TOTAL <KPI_ID, YEARMONTH> {<KPI_ID = {'PKO'}>} VALUE), KPI_ID, YEARMONTH))/

Sum(TOTAL <YEARMONTH> Aggr(Sum(TOTAL <KPI_ID, YEARMONTH> {<KPI_ID = {'ASI'}>} VALUE), KPI_ID, YEARMONTH)), '#.##0,0'))

Also find attached the application

Re: Problem with key performance indicator calculation

antoniotiman — Thu, 07 May 2015 14:19:59 GMT

Hi David,

see attachment.

Regards,

Antonio

Re: Problem with key performance indicator calculation

Mon, 11 May 2015 07:56:56 GMT

Thanks sunindia for the reply, the load of the table is a bit slow maybe the cause is the logical of the expression, but the result is good, thanks for the solution.

Bests regards.

Re: Problem with key performance indicator calculation

Mon, 11 May 2015 07:58:11 GMT

Thanks Antonio for the reply, this workaround is good too, the load of the table is a bit slow maybe the cause is the logical of the expression, thanks for the workaround.

Bests regards.