2 Replies Latest reply on Jun 11, 2012 6:27 AM by Sourabh Dasgupta

# Table Calculation - sustaining calculated values

My excel file has one columns "Action". This field has only two distinct values 1) A and 2) B. Please see the attached file. I created a calculated field for the "Index". Going down the table, whenever the value of Action field changes from A to B, I need to store that Index (which can be done), and  I also want to store the value of the previous INDEX, when it was changing from A to B. See the example below:

 Action Index Last Changed Index A 1 A 2 B 3 0 A 4 A 5 A 6 B 7 3 A 8 A 9 A 10 A 11 A 12 B 13 7

I want to calculate the "Last Index Changed". Thus at the Index=7 value is changing from A to B so I need to store when it was changing previously, thus in this case it is Index=3. Similarly at Index = 13 the value is changing from A to B and thus here I want to store the previous changed index i.e. Index=7.

Please let me know how to do this in Tableau. If we can write a "loop" then we can use variables to store these values but not sure how to do this in Tableau.

• ###### 1. Re: Table Calculation - sustaining calculated values

Here you go. Tableau does not have a looping function, but it does have PREVIOUS_VALUE(), which is a table calculation that returns the previous value of itself. So, what I did was create the following two calculations:

Index Store - this stores an index value for the current row:

IF FIRST()==0 THEN

0

ELSEIF ATTR([Action]) = "B" THEN

INDEX()

ELSE

PREVIOUS_VALUE(0)

END

Last Changed Index - this one looks up the prior value of the Index Store to get the correct result:

IF ATTR([Action]) = "B" THEN

LOOKUP([Index Store],-1)

END

Note that I had to add an ID column to your data, by default Tableau aggregates dimensions so we needed something to uniquely order the Action column.

Is this what you were looking for?

Jonathan

• ###### 2. Re: Table Calculation - sustaining calculated values

Hi Jonathan, thanks a lot for the help. I had my logic ready, but was not sure how to use in Tableau. The previous_value is what we can use. This has solved my issue. Thanks again .