Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi David,
see attachment.
Regards,
Antonio
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.
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.