Tableau calculated field that refers to its own previous lagged (-1) value to calculate

0
votes

I need help on a basic calculation that I'm unable to figure on Tableau.

I am trying to setup a calculated field that has dependency on its previous value to calculate its current value. Here is a simple example from Excel -

Sample Exhibit

As you can see, each value in a row is dependent on its previous value and multiplied by a constant.

In Tableau, when I'm trying to create a calculated field, it is not letting me refer to itself (-1 lagged value) in the code. I'd appreciate any help on how this can be resolved. Thanks in advance!

2
tableau-api
Hi everyone, thanks for your answer! Unfortunately I have tried the suggested methods, but unfortunately this doesnt work for the problem I'm solving (the sample exhibit is just 1 layer of calculations). Essentially this calculation involves referring to previous values of multiple variables (and then added as an equation), and not just 1 as in the sample exhibit. While I was able to get a functioning formula, the values I was getting is multiples of what I am expecting. Not sure if I am able to make this clear enough. – Rohit Sinha
The good news is that I have decided to not use tableau at all, cause it is not able to give me the dynamic calculations across multiple variables I was hoping to work with. So thanks! – Rohit Sinha

2 Answers

0
votes

Tableau can do this client side with a table calc. You’ll have to learn how table calcs operate from the help- especially partitioning and addressing. Then you can use the function Previous_Value() to refer to the previous value. Practice on something simple first to make sure you understand how previous value() works. Hint, the argument to that function doesn’t mean what most people assume it means

If you want to perform this calculation server side instead, then you’ll need to use custom SQL so you can specify an analytic aka windowing query

0
votes

Check the LOOKUP field to get the value from the preceding row. For example: LOOKUP(SUM([Value]),-1)

https://help.tableau.com/current/pro/desktop/en-us/functions_functions_tablecalculation.htm#lookupexpression-offset

You may need to make yourself familiar with the Table Calculation partitioning if not getting the expected result.