2 Replies Latest reply on Aug 22, 2013 8:45 AM by Jessica Le

    Create calculated field from its own field

    Jessica Le

      Is there any way i can calculate the below calculated field by using the previous result?  I can do in excel easily but not in Tableau

       

      For example,

       

      Initial = 10

       

      Row #%Calculated field
      1null10
      2-2%9.80
      33%10.09
      45%10.60
      51%10.70

       

      Here is the calculation that i would like to write in calculated field

       

      Row # 2 = 10 * (-2% + 100%) = 9.8

      Row # 3 = 9.8 * (3% + 100%) = 10.09

      Row # 4 = 10.09 * (5% + 100%) = 10.60

      Row # 5 = 10.60 * (1% + + 100%) = 10.70

       

      Thank you

        • 1. Re: Create calculated field from its own field
          Dana Withers

          Hi Jessica,

           

          This can be done using Tableau as well:

          sum(  zn([%]) + 1  ) * PREVIOUS_VALUE( [Initial] )

           

          I've attached the workbook also, so you can see what happens, but this is how it is supposed to work:

          First zn() around percentage ensures that there is always a percentage (your first value is NULL, so zn will replace it with 0).

          Then add 1 to it and sum it because it needs to be an aggregate (however it is the sum of one row, so it is the value of the row).

          Then time previous value - which returns the value of the same calculation in the previous row OR - if it is the first row - the value between the brackets. I've made that one a parameter called initial, which is set to 10 according to your description).

          Note that this is a table calculation, so needs to be set to run along table (down).

           

          Hope this helps!

           

          Dana

          • 2. Re: Create calculated field from its own field
            Jessica Le

            This is great.  Thanks so much Dana