Calculate which record to apply in a table of increasing thresholds

[nwatson-qit]

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.

[tresesco]

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

[nwatson-qit]

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

[tresesco · Accepted solution]

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

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

[Anonymous]

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

[nwatson-qit]

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