topic Re: KPI If Statement in QlikView

KPI If Statement

win_anthony — Sat, 16 Nov 2024 00:21:56 GMT

POST DELETED

Re: KPI If Statement

rubenmarin — Tue, 07 Jul 2020 16:01:26 GMT

Hi, maybe usign an aggr to calculate vendor data for each vendor individually and a rangesum to sum all vendors values:

num(RangeSum(Aggr((($(vSpendTotalInvoiceUSD_Fees_Paralegal)/$(vCountYears))
-(IF($(vBlendedRate_Paralegal)>200,
($(vNumberOfHours_Fees_Paralegal)/$(vCountYears))*200,
($(vSpendTotalInvoiceUSD_Fees_Paralegal)/$(vCountYears))
)
)
),Vendor))/($(vSpendTotalInvoiceUSD_Fees_Paralegal)/$(vCountYears))
,'#.##0%')

Re: KPI If Statement

win_anthony — Wed, 08 Jul 2020 15:44:22 GMT

@rubenmarin thank you! I tried the expression provided but the overall output is still 0. However if I make a vendor selection, it is correctly displaying the savings percentage for the selected vendor.

Are there any other minor adjustments to this expression that I can make to have the KPI reflect the overall savings percentage? FYI...I had to remove the contents of the post even though the information posted was anonymous. If you need more context, I will be happy to send a private message.

Re: KPI If Statement

rubenmarin — Wed, 08 Jul 2020 17:58:07 GMT

Hi, Maybe you only need to set $(vCountYears) outside the aggr but it's hard to say where the issue can be because each $(expression) can have anything inside.

Try adding different expressions with each part of the global expression to try to detect what is failing, ie, add one expression with:

RangeSum(Aggr((($(vSpendTotalInvoiceUSD_Fees_Paralegal)/$(vCountYears))
-(IF($(vBlendedRate_Paralegal)>200,
($(vNumberOfHours_Fees_Paralegal)/$(vCountYears))*200,
($(vSpendTotalInvoiceUSD_Fees_Paralegal)/$(vCountYears))
)
)
),Vendor))

Another with just:

RangeSum(Aggr((($(vSpendTotalInvoiceUSD_Fees_Paralegal) )
)
),Vendor))

Other with:

RangeSum(Aggr($(vCountYears),Vendor))

...