I've got a table of values in Tableau Prep and need to do a running sum.
But how?
Here's my data set:
I want to know the sum of all the income so far for each day. That's going to look something like the below:
How can we do this in Tableau Prep?
We're going to need a secondary Date column, with one row per Date for each row we are planning to add up. This will allow us to sum all of the previous days values for each day further down the line. For the first 3 days that'll look like this:
In Tableau Prep, this can be done by joining a table onto itself, and changing the join clause from an equals to a greater than or equal to.
Setting the clause to greater than or equal to - as opposed to less than or equal to - means we will have a row for each day up to and including the current day i.e., for the third day, we'll have a row for the first, second and third day. If this was less than or equal to, we'd have a row for the current day and all the following days, i.e., for the third day we'd have a column for the third, fourth, fifth, sixth and seventh day.
In Tableau Prep, my table looks like this and has 28 rows:
At this point I know I'm losing you but stick with it - all we need now is an aggregation step.
Let's sketch this out to see which fields we'll need to aggregate.
At the minute, our data is in this format (where v1 represents the value for the value for the first day and so on):
If we think back to what a running sum means we can now structure our aggregation.
In Tableau Prep, we need to aggregate on the yellow Date column by summing up the blue value / income column.
And there you have it! Tableau Prep very helpfully returns the numbers out of order but I promise its right.
This concept can then be used to create other running calculations, including moving averages.