2 Replies Latest reply on Apr 3, 2012 7:11 AM by philip.sivyer

    previous function

      Hi

       

      Just used this calculation field ...

      eg

       

      lookup(sum([amount]),-1)

       

      OK - this works - however, my question is that ....

      is it possible to find the previous rows data where not null eg

      Current Data Set

      Cpy      Amt

      A          23

      B          45

      C         

      D          64

       

      So - my requirement is with new calculated field is as follows (not sure if possible with Tableau)

      Cpy     Amt     Calculated_Field

      A         23        null

      B         45         23

      C                     45

      D         64        45

       

      Regards

      Newbie

        • 1. Re: previous function
          Alex Kerin

          I approached this by effectively 'filling down'

           

          First a calc to fill down:

           

          if isnull(attr([Amt])) then previous_value(attr([Amt])) else attr([Amt]) end

           

          So if Amt is null for that row, then use the row above. If previous_value cannot be calculated because it's the first row use Amt (this is the part in the bracket after previous_value), otherwise just use Amt.

           

          Then we can do the lookup as before:

           

          lookup([Fill Down],-1)

           

          Doing it this way avoids issues where there may be two nulls in a row. If the first row is a null, then this will result in a null in the second row for the lookup.

           

          This is the test table I used:

           

          Cpy    AmtFill Down along Table (Down)Lookup Prior along Table (Down)
          A       


          B        23.0023.00
          C        45.0045.0023.00
          D       
          45.0045.00
          E64.0064.0045.00
          F34.0034.0064.00
          G
          34.0034.00
          H
          34.0034.00
          I34.0034.0034.00
          K45.0045.0034.00
          • 2. Re: previous function

            Brilliant - thanks