topic Re: Calculate Incentives based on Sales in QlikView

Calculate Incentives based on Sales

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello Guys,

I am new to Qlik and just trying to develop on an existing app. Would like to request help to resolve an issue I am facing now:

Requirement is to calculate the incentives for each merchant, based on the sales amount per product. Sales data existing in transaction table:

[KEY_VX_TRXN]:

LOAD

Trxn_no,

TerminalID,

MerchantID,

Name,

Branch,

Service,

ProductCode,

Amount

Status,

Datetime

INCENTIVES:

Load

Category AS CATEGORY,

Service AS INCENTIVE_SERVICE,

SUBFIELD(LIMIT,'_', 2) as UPPERLIMIT,

SUBFIELD(LIMIT,'_', 1) as LOWERLIMIT,

PERCENT

RESIDENT INCENTIVES_TEMP;

ex: For a particular merchant , we calculate the sum(amount) for a month where Service=Entertainment and product=Amazon,

Incentive = 0.04* Sales Amount, if Sales 1000<=Amount<3000

Incentive = 0.05* Sales Amount, if Sales 3000<=Amount<5000

Is there a way to combine the set analysis and interval match, to make this possible?

Looking forward for expert advices.

Thank You

Re: Calculate Incentives based on Sales

shiveshsingh — Wed, 04 Apr 2018 17:30:08 GMT

Can you share your app? or sample data to work upon.

Your requirement is feasible.

if(Amount>'3000' and Amount <='1000',0.04*Sales_Amount,if(Amount>'5000' and Amount <='3000',0.05*Sales_Amount)) as Incentive.

You can use this logic to build expression.

Re: Calculate Incentives based on Sales

sasiparupudi1 — Wed, 04 Apr 2018 17:42:46 GMT

pl provide a sample

Re: Calculate Incentives based on Sales

boorgura — Wed, 04 Apr 2018 17:55:43 GMT

In the load an Interval match, and join would give the corresponding incentive percentage for each record.

you wouldn't need any conditional statements on the UI.

Re: Calculate Incentives based on Sales

jolivares — Wed, 04 Apr 2018 23:44:21 GMT

I have a similar problem and the best solution for me was as follows:

First, with the interval match, I find which range correspond for each sales amount.

Merchant_ID, RangeUp, SalesAm

Then, creating a Mapping

RangeUp, Percentaje

I apply this Mapping in order to find the percentaje

Re: Calculate Incentives based on Sales

Anonymous — Thu, 05 Apr 2018 07:19:33 GMT

The upper limit and lower limit data is not limited to the two sample provided, and hence it is infeasible to include hard coded limits in the expression. attaching the qvf file and sample data load script !

Re: Calculate Incentives based on Sales

Anonymous — Thu, 05 Apr 2018 07:21:28 GMT

Hi Ragesh,

The sales data is per transaction, not monthly, aggregated per merchant. Attached the sample script above, Is it possible to load interval match and join with this data model?

Thank You

Re: Calculate Incentives based on Sales

Anonymous — Thu, 05 Apr 2018 07:23:36 GMT

Hi Juan,

Thank You for the response. But the Sales amount is at transaction level, not monthly. So interval match is not possible, unless there is another load with monthly sales calculated. Or is is possible in any other way, which I am not aware of?

Thank You

Re: Calculate Incentives based on Sales

Anonymous — Thu, 05 Apr 2018 10:30:33 GMT

Hi Sasidhar,

Attached above

Thank You