How to create a running total/cumulative sum

Sigma Formulas and Functions
#1

We have a built in function that works perfectly for this: CumulativeSum.

In my example, I want to create a monthly running total of the [Cost] column in chronological order to plot in a line chart.

I sorted the [Month of Date] column ascending and grouped by the [Month of Date] column. I then created an aggregate calculation to Sum() the [Cost] column so I could get the [Monthly Cost]. Finally, I created an aggregate calculation to CumulativeSum() the [Monthly Cost] column, outputting the running total.

I could then use this [CumulativeSum of Monthly Cost] column as the Y-axis for my line chart.

image
image1652×996 175 KB

Important note: cumulative functions depend on the order of the given column. So if you change the sorting of that column, you could change the result!

2 Likes
#2

Hi Gareth,

What if one of the date groupings has zero records in it after a certain date? Say I have records for January through June, with a nice slope up like your graph. For months past June, I have no records, but I’d like to display the cumulativesum as just a flat line forward at that point. Is it possible?

See also this thread for context to my situation. I am thinking it can only be solved with a date spine, but have not yet found time to explore.

#3

Joining in a CSV of dates or generating dates with SQL is the solution for adding missing dates.
I think ‘CumulativeSum’ should work to fill in those dates. If not, ‘FillDown’ might do what you need.

1 Like