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: 
Not applicable

How to update the value based on a range in the another table or variable?

Given two files,where sales data contains the details of the sales according to ID or month.

Commission data gives the commission percentages according to ranges.

Now my problem is,When I select a month field in the sheet,if they are more than one sales in that month,two fields get select in the commission percentage if they belong to different ranges.

What I need is instead of that,depending the select values in the sales field,it should calculate the total costs of the sales and check the range and highlight the commission matrix the corresponding percentage.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution without creating a static interval matching table could be to use calculated listboxes with expressions like:

=Aggr(Only({$<Commision={'=[Low Value]<=Sum(Cost) and [High Value]>=Sum(Cost)'}>} Commision), Commision)

or

=Aggr(If(Sum(Cost)>=[Low Value] and Sum(Cost)<=[High Value],Commision),Commision)

QlikCommunity_Thread_165803_Pic1.JPG

QlikCommunity_Thread_165803_Pic2.JPG

QlikCommunity_Thread_165803_Pic3.JPG

QlikCommunity_Thread_165803_Pic4.JPG

hope this helps

regards

Marco

View solution in original post

3 Replies
MarcoWedel

Hi,

one solution without creating a static interval matching table could be to use calculated listboxes with expressions like:

=Aggr(Only({$<Commision={'=[Low Value]<=Sum(Cost) and [High Value]>=Sum(Cost)'}>} Commision), Commision)

or

=Aggr(If(Sum(Cost)>=[Low Value] and Sum(Cost)<=[High Value],Commision),Commision)

QlikCommunity_Thread_165803_Pic1.JPG

QlikCommunity_Thread_165803_Pic2.JPG

QlikCommunity_Thread_165803_Pic3.JPG

QlikCommunity_Thread_165803_Pic4.JPG

hope this helps

regards

Marco

Not applicable
Author

Hi Marco,

Thanks a lot.This is exactly what I was looking for.

Thanks and regards,

Srinath

MarcoWedel

glad you liked it.

regards

Marco