Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Problem with key performance indicator calculation

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.

KPI_data.jpg

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Problem with key performance indicator calculation

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

4 Replies
MVP
MVP

Re: Problem with key performance indicator calculation

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
Honored Contributor III

Re: Problem with key performance indicator calculation

Hi David,

see attachment.

Regards,

Antonio

Not applicable

Re: Problem with key performance indicator calculation

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.

Highlighted
Not applicable

Re: Problem with key performance indicator calculation

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.

Community Browser