7 Replies Latest reply on Jul 27, 2016 3:19 AM by Chris McClellan

    Calculated field based on hour in TIMESTAMP

    Stephen Davie

      Hi everyone.

      I'm a pretty regular user of Tableau, but only really just getting round to calculated fields and formulas. Just been pretty simple charts and stats before now.

       

      I've recently got stuck on an issue for over the past week and after a lot of searching online, I've not quite found a fix, although I don't think I'm too far off.....

       

      I have a large data feed and one field is TIMESTAMP - format = dd/mm/yyyy hh:mm:ss

       

      From that I am wanting to create a new Dimension with the values:

      • "Office hours" if time is between 9am and 5pm
      • "Out of hours" if time is after 5pm and before 9am i.e. not between 9am-5pm

       

      I have this formula on a new calculated field:

      IF [Time] >= #09:00:00#

      AND [Time]<= #17:00:00#

      THEN 'Office hours'

      ELSE 'Out of hours'

      END

       

      But it returns everything as out of hours:

       

      Can anyone suggest what I may be doing wrong?

       

      Following on from this, I may need to enhance the formula to include weekends as 'Out of hours' - but I'll tackle that issue if it's needed.

       

      Many thanks in advance.

        • 1. Re: Calculated field based on hour in TIMESTAMP
          Chris McClellan

          Without any data to test it on, I'd guess :

           

          If datepart('hour',[Time]) >= 9 and DATEPART('hour', [Time]) < 17

          then 'Office hours'

          else 'Out of hours'

          end

           

          For weekends use :

           

          If datepart('hour',[Time]) < 9 or DATEPART('hour', [Time]) > 17

          or

          datepart('weekday', [Time]) = 7 or datepart('weekday', [Time]) = 1

          then 'Out of hours'

          else 'Office hours'

          end

          3 of 3 people found this helpful
          • 2. Re: Calculated field based on hour in TIMESTAMP
            Ashish Chaudhari

            Hi Stephen,

             

            This thread has all what you need. This has ever aspect of this covered like lunch hours office timing and not counting other that office timings.

            Kindly refer this link - Calculate time difference with certain time range

             

            Thanks and Regards,

            Ashish Chaudhari

            3 of 3 people found this helpful
            • 3. Re: Calculated field based on hour in TIMESTAMP
              Ben Neville

              Hi Stephen - welcome to the world of calculated fields!

               

              First, let me say that the ELSE condition is a dangerous thing for newer users. The reason is that if you write a calculation that will never evaluate to anything, everything will always evaluate to the ELSE condition, and it will return results, even though it shouldn't be. Let me explain:

              You used the following calculation:

              IF [Time] >= #09:00:00#

              AND [Time]<= #17:00:00#

              THEN 'Office hours'

              ELSE 'Out of hours'

              END

               

               

              I would prefer to to see something like this:

              IF [Time] >= #09:00:00#

              AND [Time]<= #17:00:00#

              THEN 'Office hours'

              ELSEIF [Time] < #09:00:00#

              OR  [Time]> #17:00:00#

              THEN 'Out of hours'

              ELSE 'No Result'

              END

              The reason becomes fairly obvious if you use the above calculation. I'm willing to bet that every row in your data evaluates to "No Result". Using this method, you have much clearer insight into whether or not your calculation is returning the expected values. The issue in your example is the syntax - I don't think I've ever seen this exact syntax, so while Tableau isn't returning any errors in the calculation, I don't think this is really valid for doing what you are trying to do. A better option would probably be the DATEPART() function. If you are going to use many time/date comparisons and calculations, you should become intimately familiar with DATEPART(), DATETRUNC(), and DATEDIFF(). They will be your best friends if you let them.

               

              Now for the calculation I would recommend:

              IF DATEPART('hour',[Time]) >= 9 AND DATEPART('hour',[Time]) <= 17 THEN "Office hours"

              ELSE "Out of hours"

              END

               

              Notice how we're only having to take the hour as a comparison? This removes minutes from the logic at all, thus speeding up the analysis. Don't be too concerned with that off the bat, but as you become better with the tool, you can begin to design your calculations for the best performance in addition to the outputs that you want.

               

              Hope all that helps and doesn't come across as too patronizing

              2 of 2 people found this helpful
              • 4. Re: Calculated field based on hour in TIMESTAMP
                Stephen Davie

                Thanks Chris, this works an absolute treat!

                • 5. Re: Calculated field based on hour in TIMESTAMP
                  Chris McClellan

                  Now you've just got to decide who posted the correct answer and mark it as correct

                  • 6. Re: Calculated field based on hour in TIMESTAMP
                    Stephen Davie

                    I'll give it you Chris, you got in there first

                     

                    Not patronising at all Ben, it's really really helpful! I can now understand why some of my other calculations haven't worked in the past. My task for the next few days is to get my head round DATEPART(), DATETRUNC(), and DATEDIFF() as they will provide us with loads of useful insight.

                     

                    And I've never really thought about performance, some of the data sets I'll be working on in the future have got the potential to be huge, so it will always be helpful to bear this in mind.

                     

                    Thanks ever so much.

                     

                    P.S Also a big thank you to you Ashish Chaudhari - very helpful thread I've bookmarks for future reference.

                     

                    Cheers guys, Tableau Forums are the best!

                    • 7. Re: Calculated field based on hour in TIMESTAMP
                      Chris McClellan

                      Just learn a little bit as a time, as you need to .. that's the best way