3 Replies Latest reply: May 27, 2011 10:41 AM by Rob Wunderlich

# 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

• ###### 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

• ###### 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

 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

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

-Rob