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: 
nwatson-qit
Partner - Contributor II
Partner - Contributor II

Calculate which record to apply in a table of increasing thresholds

Hello all,

I am trying to write an expression to calculate which rebate should be applied for a total spend with a supplier. I am calculating the total spend with a supplier and then trying to find which record is the highest value from a linked list of records, where the total spend for a supplier is higher than the lower threshold for that record. I have attached a small qvw to highlight what I am trying to do. I suspect I am missing a layer of aggr, but I'm not sure where to put it.

Many thanks.

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Well, I guess I got your point now. Try:

FirstSortedValue({<LT={"=LT<=sum(VALUE)"}>}REBATE,-LNO)

Untitled.png

View solution in original post

5 Replies
tresesco
MVP
MVP

If you select 'A1', output comes as 2. Should not it be 4?

nwatson-qit
Partner - Contributor II
Partner - Contributor II
Author

No. The total value for A1 is 90. Higher than 50 (2) but not as high as 100 (3).

tresesco
MVP
MVP

Well, I guess I got your point now. Try:

FirstSortedValue({<LT={"=LT<=sum(VALUE)"}>}REBATE,-LNO)

Untitled.png

Anonymous
Not applicable

FirstSortedValue({<LT={"=LT<=sum(VALUE)"}>}REBATE,-LNO)

nwatson-qit
Partner - Contributor II
Partner - Contributor II
Author

Thanks, Tresesco. Thats working a treat. Thanks for your help.