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.

Hi @iron-brian,

For your first question, I’m not sure why it doesn’t default to have the aggregation checked. I tried recreating some vizs and it always had them checked.

For your second question with the area chart, that’s correct, Sigma is only going to visualize what data is populated, so if there isn’t a date in your grouping, it won’t show anything. You can resolve this by creating placeholder data (data scaffolding) for Sigma to visualize.

There are also format settings that allow you to choose how we treat Null data.
The settings apply to data that exists in the data but is null, so still wouldn’t cover the case of missing data.

1 Like