3 Replies Latest reply on Jun 29, 2018 5:34 PM by Don Wise

    Tiered Volume Based Discounting


      Hi all,


      I am trying to create some tiered volume based discounting. What do I mean by that? Based on the below two images customers XXX's cost should be $50/unit for the first ten units and then $48/unit for the 11th unit ($548 total). But what if I wanted to see customer XXX's cost under pricing option B? Their total should be $1,100. I'd ideally like to have a toggle that changes the Customer's cost depending on whether I select A or B (maybe a filter)?


      Any idea on how to accomplish this? I have the tiered pricing options as a separate sheet.





        • 1. Re: Tiered Volume Based Discounting
          Don Wise

          Hello Tarang,

          See if the attached works for you?  If so, please mark this response as correct. Thanks Don


          Screen Shot 2018-06-29 at 2.09.57 PM.png

          Screen Shot 2018-06-29 at 2.09.49 PM.png

          • 2. Re: Tiered Volume Based Discounting

            Hi Don,


            Thanks for your help. This was pretty close, but it should be incremental. So, for Option A, the first ten are priced at $50/unit and the 11th is priced at $48/unit.

            • 3. Re: Tiered Volume Based Discounting
              Don Wise

              Hello Tarang,

              I understand now what you're trying to achieve and I gave it a go with use of the Modulo function.  Essentially, it evaluates the number of units and determines whether it can divide it to 0.  If not, meaning there's a remainder, then the value falls into the next bucket (range of discounts). Then they're added together (the value that was able to be divided by 0 plus the value of the remainder) for your final discount value.


              Unfortunately, I've hit a wall with this calculation. So hopefully someone else will pick up the flag and carry it forward for you. Below is the calculation I worked up, but I'm hitting a "can't compare boolean and integer value" error:


              IIF(INT([Units]>0 % 0==0 AND INT([Units]<10 %10 == 0,.50

              + IIF(INT([Units]>11 % 11==0 AND INT([Units]<20 %20 == 0,.48

              + IIF(INT([Units]>21 % 21==0 AND INT([Units]<50 %50 == 0,.46

              + IIF(INT([Units]>51 % 51==0 AND INT([Units]<100 %100 == 0,.44

              + IIF(INT([Units]>100 % 100==0 AND INT([Units]<200 %200 == 0,.42

              + IIF(INT([Units]>200 % 200==0,.44)))))))))))))))))


              Maybe we'll both learn something!  Thx, Don