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

# Calculating Rebates

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

• ###### Re: Calculating Rebates

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

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

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