Calculating Rebates

[Anonymous]

Hi,

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

Rebates                                                            %

$0  - $10M	0.5%
$10,000,001  - $30M	1.0%
$30,000,001  - $100M	1.5%
$100,000,001  - $250M	2.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

[stevedark]

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

[Anonymous]

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

[stevedark]

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