Running sum over custom year

Q&A
,
1

I am trying to make a pivot table with a running sum for the sales column by category and item. However, using a calculated field formula it keeps accumulating the numbers all the time:

runningSum(sum({monthly_sales}), [{sales_date} ASC], [Category, Item])

with this formula:

runningSum(sum({monthly_sales}), [{sales_date} ASC], [extract('YYYY', {sales_date}) ASC, Category, Item])

It keeps accumulating and resets in January.

This formula keeps accumulating forever, it does not stop after 12 months:

windowSum(sum({monthly_sales}), [{sales_date} ASC], 11, 0, [Category, Item])

The required behaviour is to keep accumulating every twelve months starting from the start month. For example, if it started in March it should keep accumulating the numbers till next February and reset in March.

2

Hello @Rainhard, I am thinking since you want to specify the calculation to run for each year that the periodToDateSumOverTime function might be the proper method. I’ll link the QuickSight documentation for it, but I believe this will be the best route to get the value you are looking for. Let me know if this helps!

1 Like
3

Hi @DylanM thanks for the suggestion but, it did not work, it just rests if the period is a year and gives wrong numbers if the period is a month. I figured it out by shifting the months through another date axis for partitioning and then hiding it from the table this will require using the addDateTime in the second formula that I mentioned in my main question.

3 Likes
4

Thank you @Rainhard for sharing your solution with the Community! So helpful!