Support aggregation over time dimension

Options

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.

Tagged:
14
14 votes

In Review · Last Updated

Comments

Get Started with Idea Exchange


See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!