8 Replies Latest reply on Jan 28, 2013 1:38 PM by Richard Leeke

    previous epoch datetime

    aishwaryarajagopalan0

      Hi,

       

      How to determine the previous row value of an epoch datetime field, i would need help in converting unix epoch datetime field and find the previous row of it. I tried doing an epoch datetime conversion using the below calculation DDD and then found previous value of it PREV_DDD.

      Not sure if am approaching the issue in right direction, please guide.

       

      DDD

      DATEADD('second',[epoch int],DATE('1/1/1970'))

       

      PREV_DDD

      PREVIOUS_VALUE(ATTR([DDD]))

        • 1. previous epoch datetime
          Richard Leeke

          The formula for converting from Unix epoch time format looks right.

           

          To get the value of another field from the previous row you need to use the LOOKUP() function, like this:

           

          LOOKUP(ATTR([DDD], -1))

           

          The PREVIOUS_VALUE() function can take a bit of getting your head around. It returns you the value of the current field in the previous row, and it's argument is the value to use on the 1st row to kick start the whole chain. So if you defined a calculation as:

           

          PREVIOUS_VALUE(1) + 1

           

          it would give:

           

          1

          2

          3

          ...

           

          Or:

           

          PREVIOUS_VALUE(10) + 5

           

          would give:

           

          10

          15

          20

          ...

           


          1 of 1 people found this helpful
          • 2. Re: previous epoch datetime
            Shawn Wallwork

            This is an excellent explanation Richard. I wish this was what was included in the 'explanation' window instead of this:

             

            Prev Value.PNG

             

            Now it's starting to make more sense. Thank you.

             

            --Shawn

            • 3. Re: previous epoch datetime
              Richard Leeke

              Not quite excellent.  Getting it right would have been better.  Those examples should have said:

               

              PREVIOUS_VALUE(1) + 1

               

              would give:

               

              2

              3

              4

              ...

               

              Or:

               

              PREVIOUS_VALUE(10) + 5

               

              would give:

               

              15

              20

              25

              ...

              1 of 1 people found this helpful
              • 4. Re: previous epoch datetime
                Shawn Wallwork

                It's the idea that counts.

                 

                --Shawn

                • 5. Re: previous epoch datetime
                  aishwaryarajagopalan0

                  Thank for the response Richard,

                  My issue is little complicated, actually the problem is explained below:

                   

                  I have four different time stamps out of which two time stamps TSI and TSR are stored as date-time in SQL server and so have no issue in pulling it to the tableau,

                  whereas remaining two time stamps are stored as numeric(18,0) so i use below calculation

                  DATEADD(SECOND, DDDI ,'1970-01-01') as DSI,similarly for DSR.

                   

                  Then my calculation goes on determining the current row and previous row for these time stamps and do comparison.

                   

                   

                  currow  ----> will be true if DSI has null values

                  ISNULL([DSI])

                   

                  curr_row_TSR -->if DSI has null values then we use TSR

                  if [currow] =true  then [TSR] else [DSR] end

                   

                   

                  prev_TSI  ---> Determine previous TSI value

                  lookup(ATTR([TSI]),-1)

                   

                   

                  prev_DSI  ---> Determine previous DSI value

                  lookup(attr([DSI]),-1)

                   

                   

                  prev_row_TSI  ----> if currow is true then i will get prev TSI value else previous DSI value

                  if  ([currow] = true) then attr([Prev_TSI])  else attr( [Prev_DSI])  end

                  The above variable calculation throws error as "CANNOT USE NULL TYPE IN 'IF' EXPRESSION"

                   

                   

                  final calculation

                  if cur_row_TSR < prev_row_TSI then 0 else 1

                  • 6. Re: previous epoch datetime
                    Richard Leeke

                    I think that the issue is just that Tableau requires all fields involved in a calculation to be either aggregate or not aggregate, and the table calculation functions force you to use aggregates (which is why you need the attr()).

                     

                    So I think you just need to make these changes to your last two functions:

                     

                    prev_row_TSI (Prev_TSI and Prev_DSI are already aggregate, currow isn't, so I've moved the attr() functions around)

                    if  (attr([currow]) = true) then [Prev_TSI]  else [Prev_DSI] end

                     

                    final calculation (prev_row_TSI is already aggregate, cur_row_TSR isn't)

                    if attr(cur_row_TSR) < prev_row_TSI then 0 else 1

                     

                    This sort of confusion comes up all the time and I always seem to have to resort to trial and error. It isn't helped by the fact that the Tableau error messages don't actually point out what is going on. That "NULL type in if expression" messages is hopeless - it's really trying to tell you that you can't have an ATTR() of an aggregate. Once you fix that you then get the message about mixing aggregate and non aggregate until you wrap [currow] in an ATTR(). Jonathan and I are just in discussion with one of the Tableau folk about this very subject at the moment.

                    1 of 1 people found this helpful
                    • 7. Re: previous epoch datetime
                      aishwaryarajagopalan0

                      Thanks much again for the reply Richard;

                      I tried changing below options already but ended up seeing below error messages,and so am asking questions with the options in my previous message [third option]:

                       

                      Option1:

                      prev_row_TSI

                      if (attr([currow]) = true) then  [Prev_TSI]  else  [Prev_DSI]  end

                      "attr is being called with boolean, did u mean integer?

                      "cannot mix aggregate and non-aggregate comparisons or results in if expression" 

                       

                      Option2:

                      prev_row_TSI

                      if ([currow] = true) then  [Prev_TSI]  else  [Prev_DSI]  end

                      "cannot mix aggregate and non-aggregate comparisons or results in if expression" 

                       

                      Option3:

                      prev_row_TSI

                      if  ([currow] = true) then attr([Prev_TSI])  else attr( [Prev_DSI])  end

                      "CANNOT USE NULL TYPE IN 'IF' EXPRESSION"

                      • 8. Re: previous epoch datetime
                        Richard Leeke

                        Aaaaghh - yes I get caught out by that all the time - I should have tried those rather than just doing it off the top of my head. I really think the Tableau expression language has a couple of holes around booleans in cases like this.

                         

                        The (ugly) workaround I use in cases like this is to convert everything to integers with 1 for true and 0 for false. (There are cases where you need to do the same to use what should be a boolean as a filter.) Others may have a better way. So if you do this it should work:

                         

                        currow

                         

                        IIF(ISNULL([DSI]), 1, 0)

                         

                        prev_row_TSI

                        if (attr([currow]) == 1) then  [Prev_TSI]  else  [Prev_DSI]  end

                         

                        (Or something like that - again I haven't tested this!)

                        1 of 1 people found this helpful