Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

comparing with a range of values

You can do this with an if() function.

if([AvgSales] >= 11041, .04

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

))

etc

-Rob

Not applicable

comparing with a range of values

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

comparing with a range of values

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

-Rob