Forum Discussion
Another spilled array that I can't solve - Aggregating monthly into time periods
This I thought would be quite easy but hit a brick wall at each attempt.
In summary, I am trying to use a spilled array to sum up expenses into time periods.
So I have a 5 year projection and say 5 different expense lines, each with a different start and end date (end could be the end of the forecast or before) The expenses are accrued monthly but I want the flexibility to aggregate them into Applied Periods ie when they will be paid. Let's say rent. Say $100 per month but paid every three months so $300 every 3 months. I already have the monthly amounts all worked out - its is much more complex than this simple scenario and integrates, manual overrides, and fixed expenses and ones driven off factors etc but the results of these all fall into a nice Spilled Array of 10 rows by a dynamic number of columns depending of length of forecast and intervals etc.
So here will will only focus on the bit I need to resolve. I want the monthly rent to be summed into intervals of say 3 months. One row may have a 6 month interval and another a 1 month interval. As they start and (may end) in different periods, the applied period must start from x months after the first expense accrual. So if the first rent starts in month 2 and is paid 3 monthly then the first payment would total the first 3 months from period 2, paid in period 5. Each row could be different but the applied periods will be fixed for each line throughout.
I tried to create flags using Makearray which worked and my intention was to use scan and reset it at each flag. But I cannot see how to integrate 2 arrays into the scan function. The one with the expenses and the one with the flags. I tried using Map and then using scan inside the map lambda but it wouldn't aggregate. It nearly worked other than that. I tried using indexing to get the column and row of the flags to rest the scan function but referencing another array that was not part of the scan function just gave me calc errors. There are a few times I would like to use 2 arrays in a scan or a scan inside a map. Just couldn't find a way to get the row and column of the flag array (both arrays - the data to aggregate and the flags are the same size) but how do I get the row and column count inside scan to use in an index function. Every road seems to lead to a failure.
Sample attached showing my crude attempt – I only got as far as generating flags but couldn’t apply them
Shows original flags to get start date – can’t always rely on a zero value as a contract may start on 1st Jan and include 2 free months. So payment would then be due on third which would be one past the zero. So flags overcome this.
Needs to start at right time with offset from start by No of Applied periods. But can run to end of array as if a n expense ends then it will return a zero so the result will be correct - ie sum of zeros
An interesting set of data. I though of unpivoting it first before proceeding but found it more straightforward to use MAKEARRAY.
=LET( i, ROWS(data), j, COLUMNS(data), GetTotals, LAMBDA(r, c, LET( current_row, CHOOSEROWS(data, r), each_period, INDEX(periods, r), start, IFNA(XMATCH(TRUE, current_row <> 0, 1), 0) + each_period - 1, k, ROUNDUP(SUM(N(current_row <> 0)) / each_period, 0), seq₁, SEQUENCE(k, , start, each_period), seq₂, SEQUENCE(each_period, , c, -1), total, SUM(INDEX(data, r, seq₂)), check, XOR(seq₁ = c), result, IF(check, total, 0), IFERROR(result, 0) ) ), MAKEARRAY(i, j, GetTotals) )
- Patrick2788Silver Contributor
An interesting set of data. I though of unpivoting it first before proceeding but found it more straightforward to use MAKEARRAY.
=LET( i, ROWS(data), j, COLUMNS(data), GetTotals, LAMBDA(r, c, LET( current_row, CHOOSEROWS(data, r), each_period, INDEX(periods, r), start, IFNA(XMATCH(TRUE, current_row <> 0, 1), 0) + each_period - 1, k, ROUNDUP(SUM(N(current_row <> 0)) / each_period, 0), seq₁, SEQUENCE(k, , start, each_period), seq₂, SEQUENCE(each_period, , c, -1), total, SUM(INDEX(data, r, seq₂)), check, XOR(seq₁ = c), result, IF(check, total, 0), IFERROR(result, 0) ) ), MAKEARRAY(i, j, GetTotals) )
- James_BuistBrass Contributor
Brilliant. Works like a dream. I will test it more and study it> I have seen to power of Makearray but never really been able to harness it. I know I will learn a lot from this and am very grateful for your amazing answer. Thanks hugely
Been working out of a third party office with no external access for the past 2 days so only now managing to review and respond.Just tested more and there is a slight hitch. See follow on post
- James_BuistBrass Contributor
I implemented this fully and applied to to all my overhead sections where it worked flawlessly. But when I applied it to the revenue section which is basically identical, it worked on some sections and not on others. I have included a full example of a part with the 'data' section and the implemented MAKEARRY. I can't see why it would not work but it seems to leave out a large qty of values. I can't really see any key differences between the application in the overheads vs the revenues and have triple checked the refs and everything else
I have included 2 versions using my data - the full set in the attached file. One with my tweaked function - basically just broken into rows and with the data and period inputs separated out. And one with your original just with ranges applied. In my original all the inputs are actually DAsYou will see how it misses a bunch of entries from the data section.
But I just can't see any variance in the set up of the sections that work and those that don't.
Also. what I love about the solution is that it takes only 2 inputs, the source data array and the array of applied periods. SO no other inputs are interfering with it. So it must be something to do with the shape of the source data
- Patrick2788Silver Contributor
It looks like it was missing because of the XMATCH which was returning the wrong number with the approximate match.
The match part should simply look for first non-zero value to see where values begin in a row.
XMATCH(TRUE, current_row <> 0)
Entire formula:
=LET( i, ROWS(H5:CM14), j, COLUMNS(H5:CM14), GetTotals, LAMBDA(r, c, LET( current_row, CHOOSEROWS(H5:CM14, r), each_period, INDEX(D18:D27, r), start, IFNA(XMATCH(TRUE, current_row <> 0), 0) + each_period - 1, k, ROUNDUP(SUM(N(current_row <> 0)) / each_period, 0), seq₁, SEQUENCE(k, , start, each_period), seq₂, SEQUENCE(each_period, , c, -1), total, SUM(INDEX(H5:CM14, r, seq₂)), check, XOR(seq₁ = c), result, IF(check, total, 0), IFERROR(result, 0) ) ), MAKEARRAY(i, j, GetTotals) )
Additionally, you can simply your totals column with some eta:
=BYROW(H18#,SUM)