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

    Table Calculation - sustaining calculated values

    Sourabh Dasgupta

      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:

       

      ActionIndexLast Changed Index
      A1
      A2
      B30
      A4
      A5
      A6
      B73
      A8
      A9
      A10
      A11
      A12
      B137

       

      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. 

       

      Thanks in advance.

        • 1. Re: Table Calculation - sustaining calculated values
          Jonathan Drummey

          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
            Sourabh Dasgupta

            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 .