Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Calculating Rebates

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

3 Replies
stevedark
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;

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

kdot5959
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

stevedark
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:

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