Problem with key performance indicator calculation

[#1]

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.

[sunny_talwar · Accepted solution]

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

[antoniotiman]

Hi David,

see attachment.

Regards,

Antonio

[#4]

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.

[#5]

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.