Dynamic Arrays and Its Impact on Planning Analytics for Excel

In my previous blog articles, I have covered various use cases of Dynamic Arrays(DA) and some of the capabilities it offers.

In case you missed it, I would strongly recommend you go through them (see links below) before proceeding with reading this blog. 

What are Dynamic Array formulas and why you should use them

Data Analysis using Dynamic Array formulas

In continuation of that, I will cover another use case where I would further highlight the power of DA when used with PAfE.

With that being said, ever since I learnt about Dynamic Arrays in Excel, I was very fascinated and always wondered whether there are any PAfE formulas where we could leverage the same automatic spilling behavior that applies to excel DA formulas.

I always found the implicit operator(@) that now gets automatically prefixed to all PAfE formulas - to restrict the formulas to spill the results where - practically useless as none of those formulas could potentially result in spilling even without @ operator.

This was until I discovered one formula that in fact did, to my surprise. The formula is called TM1ELLIST which returns an array of elements either based on subset or an array of elements (in my TM1ELLIST formula I have overridden subset list with element list created using SEQUENCE formula). 

The DA now opens a whole lot of new possibilities for creating dynamic reports in Excel very easily. We all have gone through the agony of not being able to have a dynamic column in PAfE reports, haven't we? Well, not anymore! Just wrap the TM1ELLIST formula within TRANSPOSE formula, which is another cool DA formula, and it will transpose the results to columns.

Note: As per the IBM documentation it mentions the following: 

"TM1ELLIST does not overwrite or insert into any non-formula space like a Dynamic Report. It is up the workbook designer to consume the plural value response correctly." 

However, with DA, the formula does produce TM1RptRow function like behaviour in Dynamic Reports. And to top it up, it works totally independent of the TM1RptView function. As per the same documentation, in order to avail this formula to return an array, you need to declare this formula as a Named Range in Excel and then use the traditional CSE(Control Shift Enter) or using INDEX function approach. However, this again is no more required if you're on Office 365.