Forum Discussion
Dynamic Arrays - Extend the last logical row value in a 2D array
As always a fabulous solution - actually 2. I'm using the Index as it is actually so fundamentally simple. Not sure why I couldn't get there but added more to my war chest. I now fully get the sequence inside the index to force the rows. And, of course, xmatch in reverse!! Why did I not think of that!!
ANd the error handling then if flags are missing. Very nice and very reusable.
I now get the scan & map combo. I see the use of the new enhanced byrow - I will carry on with the older version for a bit longer until all have it rolled out but it took a few seconds to figure out how it worked as never actually just seen it in use!
So now I fully get it. The map lets you do the logical test with the flag to leave only the last values in the resulting array and then you just do the usual scan function on that using the NAs to reset the rows. That will be a great addition to my library and I can now think of a number of places I could use that kind of combination
Both fab. I may doa performance test sometime to compare them but the usage is not high so won't make much difference. I have 7 sets of these so far and don't notice anything on the calc speed.
Many thanks to you again - and Peter for his input.
Regarding this, actuals are pulled by formula as are the forecasts. But is thins case for small BS items, there will be no computation in the forecasts as they are small and so will just continue as per the latest actual. So what I want to do here is just pick up the last value in the actuals (it's a formula and the flags tell which period) and extend. But as each entity could have a different end date for actuals, I can't just take the block and drop or take either end then continue as the block end will not be straight. This accommodates when different subsidiaries have their accounts ready at different times. It enables a blending of actuals and forecasts based on actual data received.
I have a switch with 3 different option, fixed date, earliest actuals or staggered and this option is needed in the case of staggered actauls
I never mix actual hardcoded values with forecasts and all formula must start in the same column whether actuals or forecasts - hence the flags
I think the normal way of carrying forecasts forward is to use the FORECAST.ETS function, though you would have similar challenges transitioning from actuals to the forecast. At least you are less likely to find yourself propagating zeros forward in time.
I put a non-zero escalation in simply to demonstrate the functionality. Setting growth to zero or omitting it from the formula would flat line the forecast as you suggested. Yes, SCAN will pick up the gaps in actuals as you suggest.
The green conditional format makes it somewhat obvious. You could STACK your flag with the actuals as a thunk array and scan that, but I would aim to get simpler things going before embarking on that.
- I may just do that as I can't think of another solution now and need to get this out. Can revise later id a better one. But stacking as a thunk array wold work. I've been meaning to get my head around those so perhaps this is a good opportunity. I have a sample to work from and hope I can get that working. Thanks for the pointer. A week ago I wouldn't have understood what you meant!!
