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
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.
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 )
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.
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 |
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.
re - posted here - Hoping I am doing a better job explaining in this post
Charging Interest as different Groupings / Bands f... - Alteryx Community