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

comparing with a range of values

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

3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can do this with an if() function.

if([AvgSales] >= 11041, .04

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

))

etc

-Rob

Not applicable
Author

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

CustomerFromToContribution Avg Sales Per MonthContribution
0014331600 8435.73
00143316000.0010,750.00           0.01402.47
001433160010,751.0012,360.00           0.02402.471
001433160012,361.0014,540.00           0.03402.47
001433160014,541.0016,730.00           0.04402.47
001433160016,731.000.00           0.05402.47
00143316000.0010,750.00           0.00188.76
001433160010,751.0012,360.00           0.02188.76
001433160012,361.0014,540.00           0.03188.76
001433160014,541.0016,730.00           0.04188.76
001433160016,731.000.00           0.05188.76
0014331803 7789.16
00143318030.003,400.00            0.001277.97
00143318033,401.003,990.000.01560.52
00143318033,991.004,560.000.02560.52
00143318034,561.005,150.000.031277.97
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob