$ Spend into Tiers/Grouping Using Ranges with Limits

[Tommy_Alterax]

Hi Guys,

I have a portfolio of transactions where the "Daily Balance" column is duplicated see below

Tier	Portfolio	Range Start	Range End	Daily Balance
1	AAA	0	500000	2000000
2	AAA	500001	1000000	2000000
3	AAA	1000001	2500000	2000000
4	AAA	2500001	99999999	2000000

The daily balance column should reflect how much was used in that Particular Tier - For example the 1st Row Tier 1 the 'Daily Balance of $2M is greater than the Range End of 500k - So the daily balance should reflect 500k for Tier 1 (amount used)

Desired outcome something like as follows : 

Tier	Portfolio	Range Start	Range End	Daily Balance (Bal Used)	Bal Carried Fwd Next Tier
1	AAA	0	500000	500000	1500000
2	AAA	500001	1000000	499999	1000001
3	AAA	1000001	2500000	1000001	0
4	AAA	2500001	99999999	0	0

I.E now we can see the daily balance that was used in each range . 

Note I will often only have 2 tiers for a portfolio however, my daily balance will only stay in Tier 1 as it is small - Any solution will need to catch this scenario - 

Sample file attached below 

[ed_hayter]

Ok I've given this a go. But I was confused by your input as i couldnt get the logic of range start range end resulting in those carry on's.

I've gone with an iterative macro that fills in a tier at a time based on conditional logic of the ranges.

Also an image of the output of the macro. Once I changed the range parameter of tier 2 to get the 499999 used

With the previous ranges i couldnt understand why more balance wasnt used.

[Tommy_Alterax]

Thanks Ed appreciate your efforts here on , I'm def not as advanced as you to follow the solution - the Range start - Range End dictates how much of the daily balance can be used - In the raw data (First table) the daily balance is just duplicated at 2000000 for every tier. 

However only 500000 could be used in Tier 1 as the range finishes at 500000 - Therefore my daily balance for Tier 1 is 500000

For Tier 2 we now carry down (2000000-500000) / 1500000 however, in Tier 2 we can only use 499999 as the range runs from 500001 to 1000000

This leaves us with a balance carried down to Tier 3 of 1000001 (1500000 carried down from Tier 1  - 49999 used in Tier 2 )

[ed_hayter]

No worries Tommy, I don't think my solution is ideal (someone might come up with a more efficient approach) but the way my logic works is basically if the balance carried down is within the range then process it. If the amount carried down is greater than the range end then process the max amount and carry down the leftover otherwise if the balance is less than range start process nothing and carry the total down to a new tier.

What i still don't understand is tier 2. Why 499999 for a range of 500001 to 1000000. Once i understand that i can adjust the logic to account for that.

At the moment the logic is that because each row needs a filled in tier above it runs sequentially a tier at a time filling in how much is processed then calculating how much is carried on. If you were to scale this solution then we would have to group the transactions by an ID in the multi-row formulas or batch the process and send in a transaction with all its tiers at a time into the iterative macro.

[Tommy_Alterax]

Of your balance carried down to a Tier 2 range of 500001 to 1000000 - You could only utilise 499999 of that balance carried down i.e (1000000 - 500001 = 499999)

My communication of this problem could be better let give a little more context

Imagine the client is charged a different price for each range / Tier 

Having Duplicate Daily Balances at 2000000 for every tier is incorrect 

Need to know how of the 2000000 was used in Tier 1 - 

If Greater than the Range End then of Daily balance passes down to Tier 2  and Daily balance = Range End  (Thats all we could use we hit the limit / range end)

If daily balance less than the Range End then nothing would pass to Tier 2 or Tier 3 and our daily balance would just be = Tier 1 daily balance however, Tier 2 and Tier 3 daily balance should then be 0

I f passes a balance to Tier 2 - repeat process for Tier 2  .......................... The max that could be utilised in Tier 2 499999  (1000000 - 500001 = 499999) - It could be less than this or it could be more and a balance passes down to Tier 3

Tier	Portfolio	Price Being Charged	Range Start	Range End	Daily Balance
1	AAA	2.1	0	500000	2000000
2	AAA	1.1	500001	1000000	2000000
3	AAA	0.5	1000001	1500000	2000000

[ed_hayter]

Still not totally clear on the logic - apologies - it's quite hard to follow over text. I have updated the workflow and now the macro works as expected with your given input to the output (just changed some of the multi-row logic). Would probably need to be tested on other examples to see if it is robust.

[Tommy_Alterax]

re - posted here - Hoping I am doing a better job explaining in this post

Charging Interest as different Groupings / Bands f... - Alteryx Community