Calculate field on distributed tiers between min and max

[-TheRavenKing-]

Morning

I've been struggling with a calculation, that I couldn't find anywhere.

Lets imagine I have the following table on QS:

If I need to calculate 350,000 on where the first 100,000 are calculated at 10%, the following 100,000 at 9% and so on, what would be the correct approach for this?

the calculation needs to distribute the 350,000 over the tiers and calculate the respective value against the charge.

The number of tiers could change, depending on the case. Same with the amount that needs to be distributed, so it needs to be dynamic

the result for this would be the following:

Found this, but wouldn't work for me as the values are dynamic:

https://community.qlik.com/t5/QlikView-App-Dev/Tiered-Commission-based-on-sales/m-p/8591

Thanks in advance for your help.

[marcus_sommer]

It's probably not yet your final solution but demonstrates a way how to approach to such challenge:

sum(aggr(rangemin(350000 - min, max - min) * charge, tier))

So this means with the aggr() is a calculation per tier performed and the outer sum() aggregates the single calculations again. More difficult as this is to define the values per tier. Instead of multiple nested if-loops to query the value against min and max a range-function is used. In my experience are range-functions very powerful and you may also need to nest multiple ones to extract the right value.

Beside this you may need some adjustments to the min/max values to minimize the difference of 1 between max and min of following tiers, for example with 99,999.9999999 as max for tier 1 and 100,000 as min for tier2.