Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can do this with an if() function.
if([AvgSales] >= 11041, .04
,if([Avg Sales] >= 9471, .03
))
etc
-Rob
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 |
I'm having trouble understanding. Can you post an example QVW?
-Rob