Support aggregation over time dimension
Description of the enhancement:
Currently if the Source has a time dimension then the result must also when using SUM. Users would like to be able to aggregate over time dimension.
How this would help our business processes:
It would remove the need to do the following workaround.
If you have:
Result = A.a[Sum A.b]
Result Applies To: List X, Time Scale: N/A
A.a Applies To: nothing, Time Scale: Day
A.b Applies To: nothing, Time Scale: Day, Format: List X
To workaround it:
create an intermediate line item
int = IF ITEM(List X) = A.b THEN A.a ELSE 0
int Applies To: List X, Time Scale: Day
Alt Result = TIMESUM(int)
There are a couple of potential issues with the suggested workaround:
1. The extra data volume incurred by the intermediate line item, particularly as its size is the product of the size of list X and the number of days in the timescale.
2. Performance ... for any change to the source line item, the intermediary line item will recalculate a full slice across list X, and the TIMESUM then has to iterate across a whole slice of the intermediary line item i.e. across the whole timescale.
In practice, these might not be an issue ... it depends on the sizes of list X and the timescale.
Another potential workaround is to map the source line item to an intermediary line item that applies to a fake day list using a LOOKUP by Day, then use a normal SUM formula. The advantage of this over the above workaround is that it doesn't incur much memory overhead. The disadvantage is that you need a fake day list, and an extra line item to use as the relation between the fake day list and the timescale.
Get Started with Idea Exchange
See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!