Previous_Value Table Calculation

Per the Tableau documentation, the PREVIOUS_VALUE table calculation:

“Returns the value of this calculation in the previous row. Returns the given expression if the current row is the first row of the partition.”

PREVIOUS_VALUE is not a calculation I use frequently, however, it is very helpful in specific situations. This blog post will walk through how the calculation works and some example use cases:

• Comparing PREVIOUS_VALUE to LOOKUP
• Creating a standard running sum
• Creating a custom running sum
• Creating a running product
• Creating a comma separated list of items

Comparing PREVIOUS_VALUE to LOOKUP

To help understand what the PREVIOUS_VALUE calculation does, it can be helpful to understand what it does not do by comparing it to a much more commonly used calculation, LOOKUP, when used to return a value from the prior row.

The two tables of data, below, show years and corresponding sales (let’s imagine they are millions of $). The table on the left has the following calculation in the second column:

LOOKUP(SUM[Sales], -1)

The table on the right has this calculation in the second column:

PREVIOUS_VALUE(SUM([Sales])

Both table calculations are computing along [Year]. i.e “Table Down”.

The LOOKUP calculation is showing the Sales value from the prior row.

The PREVIOUS_VALUE calculation is showing the value of this calculation (i.e. the PREVIOUS_VALUE calculation) in the previous row, all the way up to the first row. For the first row (year 2011), there is no previous row to take a value from, so at that point the calculation shows the argument that is passed to the calculation, which, in this case, is SUM([Sales]), which it takes from the current row (year 2011).

This is reflected in the image below that shows where each calculation is pulling values from:

Creating a Running Sum Calculation

PREVIOUS_VALUE can be used to replicate a RUNNING_SUM calculation. The “PV Running Sum” calculation used in the following table is shown below:

SUM([Sales]) + PREVIOUS_VALUE(0)

The calculation takes the Sum of Sales from the current row and adds that to the value of this calculation in the row above.

For the first row, the Sum of Sales is added to zero, which of course equals the Sum of Sales for that row (year 2011).

Why not use a RUNNING_SUM?

One advantage to using PREVIOUS_VALUE in this case is that you have flexibility to customise the calculation. For example, you could make the running sum of sales restart at certain years, let’s say in 2014 and 2018, as shown below:

You can see the breaks in the downward arrows in the image above, where the running sum is restarting.

This is achieved with the following calculation:

IF ATTR([Year])=2014 OR ATTR([Year])=2018 THEN
SUM([Sales])
ELSE
SUM([Sales]) + PREVIOUS_VALUE(0)
END

This is a somewhat arbitrary example, but the purpose is to show that it is possible. More complex examples might include basing the running sum logic on comparisons to other measures, such as sales, profit, tax and so on.

Create a Running Product Calculation

In the below example, PREVIOUS_VALUE has been used to create a running product calculation.

This is calculated by taking the result of the “PV Running Product” calculation from the prior row and multiplying this by the Sales value for the current row. Below is the calculation used:

SUM([Sales]) * PREVIOUS_VALUE(1)

So, for example, for 2015 the PV Running Product is effectively:

1 x 2 x 3 x 4 x 5 = 120

For the first row, sales for the current row are multiplied by 1, which is the argument passed into the calculation.

The directional arrows in this case are the same as for the Running Sum calculation shown earlier, except this time the operation is a multiplication of the two values instead of an addition.

Creating a Comma Separated List

In this example, PREVIOUS_VALUE has been used to create a delimited list of all the Sub-Categories in the view:

Here’s the calculation:

IF FIRST()=0 THEN
ATTR([Sub-Category])
ELSE
PREVIOUS_VALUE(“”) + “, ” + ATTR([Sub-Category])
END

If it’s the first row, then the Sub-Category is returned: “Art” in this case.

If it’s not the first row, then take the value in the prior row, add a comma, then add the sub-category from the current row.

A LAST()=0 filter can be added to only show the final row, as in the view below, so we see the complete list only.

The full list can be accessed via a tooltip, which could be used as an alternative to showing a table of items in a ‘viz in tooltip’:

With this list of items as a single value being associated with one mark in the view, it could also now be used in a dashboard action or to update a parameter using a parameter action.

I hope this is useful. If you have any feedback or comments, please add them below. Thanks for reading!

Marc Reid
Twitter | Linked In | Tableau Public