Need help for dinamic set analysis calculation

[Oriol_]

Hi experts, I need some help to calculate with this scenario:

With a fact sales table, I need to calculate a discount based on sum sales for the selected dates, then for that total, I have to found the correct discount based on a table with amount tranches. 

this is an example what I have now in a front end table:

2 first columns for the fact table, and the last 3 is a LEFT JOIN for the amount tranches table  (this is stored in table so will change by group of customer and dates)

obviously I need only one record sales by customer with the correct T_discount, based on the sales :

for instance, customer A with sales 829,24 € will fall in the tranches 501 to 950 € so will have 25 % discount.

thanks in advance for your help,

Oriol.

[OmarBenSalem]

You don't need a left join.

Ratehr use intervalmatch to match the sales by customer with your intervals.

Check these for step by step guidances :

https://community.qlik.com/t5/Qlik-Design-Blog/Using-IntervalMatch/ba-p/1475510

https://www.tutorialspoint.com/qlikview/qlikview_intervalmatch.htm

https://help.qlik.com/en-US/sense-kubernetes/Subsystems/Hub/Content/Sense_Hub/Scripting/match-interv...

[Oriol_]

Hi @OmarBenSalem   thanks for your fast answer, 

yes, I know intervalmatch but I guess is for use at script, not at front end.

so because I dont have a fixed sum of sales, but a dinamic computed sales at front end table, resulting a needed  calculated "interval match" at set analysis front end.

thank you

Oriol.

[Oriol_]

Hello, any update for this, please?

thks

[marcus_sommer]

It's not solvable with a set analysis else you will need a nested if-loop approach querying the sum against each range, maybe with something like:

if(sum(Sales) >= subfield(concat([T from], '|'), '|', 1),
    subfield(concat([T Discount], '|'), '|', 1);
if(...

It's definitely not a nice way ... therefore I suggest to check if a script-solution isn't more suitable. Usually a discount depends on a single order or on the orders of a certain period like a week or month - and in such cases it could be resolved within the script.

- Marcus