Cumulative or Running Total

Looking to create a visual with a cumulative count of items (y-axis) by date (x-axis). Unsure best method to accomplish this. Essentially I’ll end up with a visual with an ever increasing total over time. Would like to be able to change the x-axis by day/week/month/year as needed.
Picture below isn’t showing the column I’d like of a running total, but week 2021-05-30, would be 38 + 36 = 74, and so on.
image

Hi @iron-brian !

All you need to do is create a second calculation for the Cumulative Sum based on your count field. Then create a child element for the running sum viz. (for example CumulativeSum([CountDistinct of Order Number]) )

If you only want it to add the current and prior value, you can use the LAG function to offset 1. (For example [CountDistinct of Order Number] + Lag([CountDistinct of Order Number], 1) )

1 Like

Thank you! I think the key for me was using a table with groupings instead of a pivot table! Much appreciation for the help.

Follow up question (and I admit I haven’t read the documentation yet). How would I be able to add series to this cumulative result. Say in your example I had customer groups, and I wanted a stacked area chart, with each customer group a different area/color. Is that possible? I seem to only be able to add aggregate calculations to my groupings, which First/Last won’t cut it for what I am trying to accomplish.

Thanks!

That’s pretty simple! Just add your customer groups field to a table grouping above your count field. The grouping tells Sigma to restart the running in the running sum.

Then build the area chart the same way, but put the customer groups on the coloring.

1 Like

Great thank you!

What caught me this time is that I needed to expand the “Show Source” area at the bottom and check the box associated with the new grouping column. What is the reason this does not default to being checked and available to plot with?

@data_katrina I am here with another follow-up. With the new groupings, when I go to stack the areas it falls apart because the cumulative doesn’t carry forward when the date doesn’t exist for that group. Outside of creating a date spine/date table, is there a way to fix this.

The none stacked version shows this well when certain groups stop showing once a month with no data is crossed.