topic comparing with a range of values in QlikView

comparing with a range of values

Wed, 25 May 2011 20:05:01 GMT

Hi, I need help in arriving at the contribution value below.

I am calculating Avg Sales for a customer = Total Net Sales / # of months ( the number of months changes depending on what date range the user selects)

This Avg Sales is to be compared with a range of values to get the contribution. Each customer has his own range of values.

For example, if Avg Sales = $9,822.30 then contribution should be 0.03

From To Contribution

$0.00 $6,680.00 0.00

$6,681.00 $8,040.00 0.01

$8,041.00 $9,470.00 0.02

$9,471.00 $11,040.00 0.03

$11,041.00 $0.00 0.04

comparing with a range of values

rwunderlich — Thu, 26 May 2011 00:00:06 GMT

You can do this with an if() function.

if([AvgSales] >= 11041, .04

,if([Avg Sales] >= 9471, .03

))

etc

-Rob

comparing with a range of values

Thu, 26 May 2011 17:49:18 GMT

The Total AvgSales, From and To values change based on Customter selected.

I used if('$(vAvgSales)'> From OR '$(vAvgSales)'< To, Contribution) but does not work.

Since 8435.73 falls in From/to value range 0.00 - 10,750.00 I expect contribution field

to be filled up with 0.01 for this customer14331600

Customer	From	To	Contribution	Avg Sales Per Month	Contribution
0014331600				8435.73
0014331600	0.00	10,750.00	0.01	402.47
0014331600	10,751.00	12,360.00	0.02	402.471
0014331600	12,361.00	14,540.00	0.03	402.47
0014331600	14,541.00	16,730.00	0.04	402.47
0014331600	16,731.00	0.00	0.05	402.47
0014331600	0.00	10,750.00	0.00	188.76
0014331600	10,751.00	12,360.00	0.02	188.76
0014331600	12,361.00	14,540.00	0.03	188.76
0014331600	14,541.00	16,730.00	0.04	188.76
0014331600	16,731.00	0.00	0.05	188.76
0014331803				7789.16
0014331803	0.00	3,400.00	0.00	1277.97
0014331803	3,401.00	3,990.00	0.01	560.52
0014331803	3,991.00	4,560.00	0.02	560.52
0014331803	4,561.00	5,150.00	0.03	1277.97

comparing with a range of values

rwunderlich — Fri, 27 May 2011 14:41:47 GMT

I'm having trouble understanding. Can you post an example QVW?

-Rob