6 Replies Latest reply on May 20, 2017 4:23 PM by Okechukwu Ossai

    Next Value

    Manish Chitnis

      Hi,

       

      I'm trying to solve a time series problem in Tableau where I'm trying to identify if post a local minima, I am headed to a new low or a new high.  All solutions I can think of require being able to access the next value in the same field.   Does anyone know of any tricks/tips to get that value?

       

      Please see attached workbook.  I show precalculated values to show what the value should be, but getting there in Tableau is proving to be a challenge.

       

      E.g. in the attached workbook, RecoveryUpDown column should state Down for July 2011 or Jan 2012. 

      Which I could do if there was some sort of a way to access next value for that column with the following calc definition:

      IF [In PullBack]=0 THEN 'Up'

      ELSEIF [New Low]='New Low'  THEN 'Down'

      ELSEIF NEXT_VALUE(0) = 'Down' THEN 'Down'

      ELSE NULL

      END

        • 1. Re: Next Value
          Justin Larson

          Based on this workbook, you appear to be familiar with window calculations. I believe what you are after is LOOKUP.

           

          LOOKUP([RecoveryUpDown],-1)

           

          Gives you the value in RecoveryUpDown from the previous row in the [window] partition.

          • 2. Re: Next Value
            Manish Chitnis

            Justin,

             

            Looking for the next value rather than previous value.  Also lookup doesn't work for the same column

            • 3. Re: Next Value
              Justin Larson

              Change -1 to 1 to make it next value instead of previous. The int parameter in that calc is the offset, so you can navigate to whatever row within the partition you want, dynamically if you start to introduce first(), last() and size().

               

              You can use the existing RecoveryUpDown as a helper calculation (rename it if you want) that does not need to be shown. Then use the lookup against that to do your conditional logic and actually return the result.

              • 4. Re: Next Value
                Manish Chitnis

                Thanks Justin.

                 

                However, LOOKUP doesn’t allow you to navigate on the same column.   This is a recursive function - you cant write it with helper column.

                • 5. Re: Next Value
                  Prayson Wilfred Daniel

                  LOOKUP([Field],[Off-set]), LAST() and FIRST() are your friends.

                   

                  Setting offset to -1(i.e. LOOKUP([RecoveryUpDown],-1) will give you 1 set back/previous RecoverUpDown value), 0( look current value) and 1(1 set forward/next value).

                   

                  LOOKUP([RecoveryUpDown],0) is quite handy as is self looking, namely looking at the current value.

                   

                  FIRST()=0 will get you the first/old value and Last()=0 will get you the last/earliest value.

                  • 6. Re: Next Value
                    Okechukwu Ossai

                    Hi Manish,

                     

                    You are almost there, just a little bit of tweaking your current formulas. I'm not entirely sure if you only want to make the next null row "Down" if the previous row is "Down" and there is a local minima. Or you would want to recursively fill every null row with the previous value until a different non-null value is encountered. I have created solution for each option below.

                     

                    Option 1. Create calculated field [RecoveryUpDown_v2]

                    IF ISNULL([RecoveryUpDown]) AND LOOKUP([RecoveryUpDown],-1) = "Down" AND NOT ISNULL([Local Minimum]) THEN "Down"

                    ELSE [RecoveryUpDown]

                    END

                    This formula fills the next null row post local minima with "Down" if the previous value is Down.

                     

                    Option 2. Create calculated field [RecoveryUpDown_v3]

                    IF ISNULL(LOOKUP([RecoveryUpDown],0)) THEN PREVIOUS_VALUE([RecoveryUpDown])

                    ELSE [RecoveryUpDown]

                    END

                    This formula recursively assigns a null row with the value of the previous non-null row.

                     

                    Let me know if this meets your requirement or if you will want me to tweak the formulas further. See attached workbook.

                     

                    Hope this helps.

                    Ossai