Getting a cumulative total

Hi Klipfolio!

In this post, we show an example of how to aggregate data at a monthly level, then get a running total(cumulative).
Lets take a look at this datasource here.

The dates in column A are in MM/dd/yy format, and what we want to do is sum the values per month, then get a running total of this.
We can't just do this running total directly using the CUMULATIVE function, because any applied grouping we do will be done after the CUMULATIVE function is applied.

So what we are going to do is create a hidden table, do all of our applied grouping, filtering, aggregating and sorting in this table, then reference the results directly in the chart itself.

-To make this table hidden, in the Properties panel of each column, check this box;

-Column 1 is just column @A:A from the datasource. Make sure the Properties for the Date/Time format are set to output in a monthly format;

-We can also apply a filter to this column, to remove the first row(the column header);

-For the Value column, the formula is just @B:B

-Do an applied group to the first column to get the aggregated Value by the month

-Do an applied sort on the first column to get the dates sorted Chronologically

Next, on the bar chart itself:

And voila!
The CUMULATIVE function is now safe to use, since it is the last thing being applied.



Please sign in to leave a comment.