3 Replies Latest reply: May 25, 2018 11:15 AM by Steve Dark RSS

    Calculating Rebates

    Daniel Gorman

      Hi,

       

      New user to Qlik and I am looking for some help with regards to calculating tiered rebates i.e.

       

      Rebates                                                            %

      $0  - $10M0.5%
      $10,000,001  - $30M1.0%
      $30,000,001  - $100M1.5%
      $100,000,001  - $250M2.0%

       

      i'm unsure of how to create the buckets and filter the total spend into each to create the calculation, so far I only have bucket for total spend.

       

      Any help would be appreciated

        • Re: Calculating Rebates
          Steve Dark

          Hi Daniel,

           

          Are the values to be bucketted in a single field in the source data?  If so you will want to create the rebate field during load, like this;

           

          LOAD

            Value,

            num(if(Value < 0, 0,

              if(Value <= 10000000, 0.005,

              if(Value <= 30000000, 0.01,

              if(Value <= 100000000, 0.015,

              if(Value <= 250000000, 0.02,

              0.03))))), '#,##0.0%') as [Rebate Pct],


          You can then do a Preceeding Load to get the actual rebate, like this;


          LOAD

             *,

              Value * [Rebate Pct] as Rebate

              ;

          LOAD

              Value,

              num(if(value etc...

           

          If you need to aggregate to get to the value that you need to calculate the rebate on, then you probably want to use a GROUP BY in the load script, from which you can then calculate the rebate value.  That's a touch more involved, but just a number of small steps to get to the right result.

           

          Hope that helps,

          Steve

            • Re: Calculating Rebates
              Daniel Gorman

              Hi Steve,

               

              Thanks for the reply, the data is transactional data so there is no rebate field, my original idea was to create it using numerous if statements

                • Re: Calculating Rebates
                  Steve Dark

                  So, do you need to take into account all transactions to date to decide which rebate bucket a customer is on, or is it within a certain time frame?

                   

                  If it is for all time you could do it in the load script, using a RESIDENT load.  This loads from a table which has already been loaded and allows you to do further manipulation.

                   

                  So, if you had a CustomerID and a number of Values you needed to total up, it would be:

                   

                  CustomerRebates:

                  LOAD

                    CustomerID,

                    sum(Value) as [Customer Value To Date],

                    num(if(sum(Value) < 0, 0,

                      if(sum(Value) <= 10000000, 0.005,

                      if(sum(Value) <= 30000000, 0.01,

                      if(sum(Value) <= 100000000, 0.015,

                      if(sum(Value) <= 250000000, 0.02,

                      0.03))))), '#,##0.0%') as [Rebate Pct]

                  RESIDENT SalesTable

                  GROUP BY CustomerID

                  ;


                  In the front end of the application you could then get the total value of Rebates as:


                  sum(Value*[Rebate Pct])


                  Or the total value with rebates removed as:


                  sum(Value*(1-[Rebate Pct]))


                  Hope that helps?


                  Steve