6 Replies Latest reply on Oct 13, 2016 8:31 AM by Jason Olson

    Time comparisons

    Martin Ocando

      I need to select a specific timeframe in a calculated field, and I can't find a way to select a specific hour:minute figure.

       

      I can do IF DATEPART('hour',[MyDate]) >= 6

       

      and it works, for 6 AM, but what if I want to do 06:30?

       

      I tried with #06:30#, but gives the error that is not a valid date.

       

       

      Thanks for your help

        • 1. Re: Time comparisons
          Ryan Slagle

          Hi Martin,

           

          Try adding an additional bit of logic for the minutes:

           

          IF DATEPART('hour',[MyDate]) >= 6 AND DATEPART('minute',[MyDate]) >= 30 THEN something END

           

          -Ryan

          • 2. Re: Time comparisons
            Shawn Wallwork

            You need to learn how Tableau 'thinks'. So this works:

             

            IF DATEPART('hour',[MyDate]) >= 6

             

            Now you want it to be 6:30, right?

             

            IF DATEPART('hour',[MyDate]) >= 6 AND DATEPART('minute',[MyDate]) >= 30 THEN ....

             

            You need to break it all down into parts, Tableau (and most software) don't read time/dates without a lot of help.

             

            Cheers,

             

            --Shawn

            • 3. Re: Time comparisons
              Jason Olson

              I think this is what you are looking for:

               

              DATEPART("hour",DATETRUNC("minute",NOW()))

               

              Just replace NOW() with your datetime. The result of this will be a single number that represents the hour. 6:30 will end up as 6 if am and 18 if pm.

              • 4. Re: Time comparisons
                Martin Ocando

                I thought about that, but is not working. Looks like is selecting all events happened after 6:30 AM, and every other hour, past 30 minutes, but not before. Looks weird.

                • 5. Re: Time comparisons
                  Martin Ocando

                  Can you elaborate more on that one? What should I use for comparison?

                   

                  What I need is a way to select a range. It should be something like:

                   

                  [Shift]

                  IF [MyDate] >= "06:30" AND [MyDate] <= "18:30" THEN "Day" ELSE "Night" END

                  • 6. Re: Time comparisons
                    Jason Olson

                    I have attached a full sample of what I think you are looking for. If its not quite correct just let me know.

                     

                    I started by creating two parameters. One for the start time and one for the end time. I just have them as Date & Time format as I was too lazy to figure out how to get that to change... If its critical let me know and I'll try and fix it.

                     

                    From there I took the date & time in my data set as well as the start and end times and converted them to decimals in order to create an easy and straightforward calculated dimension to pull it all together. The calculation is as follows:

                     

                         INT(DATEPART("hour",[Date])) + INT(DATEPART("minute",[Date]))/60

                     

                    That final calculated dimension is called "Time of Day Indicator" and is simply defined as the following:

                     

                         IF [Time Decimal] >= [Start Time Decimal] AND [Time Decimal] <= [End Time Decimal] THEN

                             "Day"

                         ELSE  

                             "Night"

                         END

                     

                    Hope that helps!