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

​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 themShows 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   Read More

​