Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
pl provide a sample
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.
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
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 !
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
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
Hi Sasidhar,
Attached above
Thank You