topic Re: How to update the value based on a range in the another table or variable? in QlikView

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

Fri, 20 Nov 2020 18:02:30 GMT

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.

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

MarcoWedel — Wed, 27 May 2015 21:29:35 GMT

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)

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

hope this helps

regards

Marco

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

Thu, 28 May 2015 05:02:51 GMT

Hi Marco,

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

Thanks and regards,

Srinath

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

MarcoWedel — Thu, 28 May 2015 11:39:49 GMT

glad you liked it.

regards

Marco