Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
win_anthony
Partner - Contributor III
Partner - Contributor III

KPI If Statement

POST DELETED

Labels (3)
1 Solution

Accepted Solutions
rubenmarin

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))

...

View solution in original post

3 Replies
rubenmarin

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%')

win_anthony
Partner - Contributor III
Partner - Contributor III
Author

@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. 

rubenmarin

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))

...