Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculate Incentives based on Sales

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;

d3f4e33db657425a9544b5f2b919c763.png

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

8 Replies
shiveshsingh
Master
Master

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.

sasiparupudi1
Master III
Master III

pl provide a sample

boorgura
Specialist
Specialist

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.

jolivares
Specialist
Specialist

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



Anonymous
Not applicable
Author

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 !

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Hi Sasidhar,

Attached above

Thank You