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

    comparing with a range of values

    Rajashree Reddy

      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
          Rob Wunderlich

          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
              Rajashree Reddy

              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