Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

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
Highlighted
MVP
MVP

Re: Calculate which record to apply in a table of increasing thresholds

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
Highlighted
MVP
MVP

Re: Calculate which record to apply in a table of increasing thresholds

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

Highlighted
Partner
Partner

Re: Calculate which record to apply in a table of increasing thresholds

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

Highlighted
MVP
MVP

Re: Calculate which record to apply in a table of increasing thresholds

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

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

Untitled.png

View solution in original post

Highlighted
vsudhakar
Contributor III

Re: Calculate which record to apply in a table of increasing thresholds

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

Highlighted
Partner
Partner

Re: Calculate which record to apply in a table of increasing thresholds

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