Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Calculating Rebates


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

3 Replies
Not applicable

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;



  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;



    Value * [Rebate Pct] as Rebate




    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,


Not applicable

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

Not applicable

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:




  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]




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?