Late last year I started to actively help out on the Tableau Forums. What a great decision! I’d forgotten how much fun it could be to (1) pick up a discrete challenge; (2) help others out; and (3) learn so much more in the process.
One of the questions I recently chipped in on was about circular references in a sequential calculation. The background to the question is really interesting and I ended up spending a few hours digging into epidemiological models, but that’s a different story! Whilst trying to help I took a fresh look at the PREVIOUS_VALUE function in Tableau. I have to admit, prior to this I had thought that PREVIOUS_VALUE(x) was just the same as LOOKUP(x,-1) … turns out that isn’t the case!
LOOKUP(x,-1) will return the previous value of the expression, x, in the “window” of results being presented in the view. So if you’re presenting a monthly sum of [order value], LOOKUP(SUM([order value]),-1) can give you the sum of order value from the prior month*. Handy eh!
PREVIOUS_VALUE(x) is actually saying get the result of this entire calculation from the previous result in the window. And whilst LOOKUP returns NULL on the first result, PREVIOUS_VALUE will return the passed in expression, x, as the first result.
Your initial thoughts might be that these aren’t much different, but the real power is when you want to do more with your calculation. Let’s say you have:
yA = 2 * LOOKUP(x,-1)
yB = 2 * PREVIOUS_VALUE(x)
In the case of the LOOKUP above you’re always just looking up the last value of x and then multiplying by 2. With PREVIOUS_VALUE you’re starting with x and multiplying by 2, but then each subsequent call is actually looking up the last value of the overall expression – i.e including the previous multiplication by 2 – then multiplying by 2 again.
So the first yB = 2 * x.
The second yB = 2 * (2 * x) … and so on.
That’s pretty powerful!
The Tableau expression help gives a couple of great examples. The first is to calculate a running sum, and of course you could use RUNNING_SUM for that instead. The second example is pretty cool though – a running product!
I built this viz on Tableau Public to help illustrate the difference in the example I used above. Feel free to download it and have a play.
Edit:
During subsequent discussions on Twitter Jim Dehner (Tableau Ambassador and top forum contributor) pointed out some additional differences with PREVIOUS_VALUE. I’m yet to dig into these but leave them here in case they help you … “previous value will return the single previous value and can not be chained or used in certain table calculations like running sums - Lookup is a table calculation that moves from the current “cell” location up, down, left or right by any increment – it can be nested”. I also need to credit Jim for reminding me about the handy example in the Tableau calculation wizard / function help that I included above.
Footnotes:
* with table calculations you get to define how they traverse the window. If you’re displaying a table of months, where each month is a row in your view (i.e. month is on the rows shelf), and you want to LOOKUP the value from the prior month, then you could compute the lookup using “table down”. That way the window progresses down the list of months, and you’re looking up the value from the row above. Table across is often used when you’re calculating a cumulative total on a line graph with time on the horizontal axis (columns). You can define more complex ways to compute the order of the window. And you can specify when to restart the calculation, e.g. to get a separate monthly running total per year.