3 Replies Latest reply on Oct 25, 2016 12:15 PM by Jonathan Drummey

    Hour and Minute sort

    Dan Gordon
    Dan Gordon Oct 24, 2016 12:54 PM

      Hello all,

       

      This may be a very easy solution and if so please excuse my ignorance.  I am trying to filter my dataset by hour/minute combination.  I have millions of records with times covering all 24 hours.  I want to filter my records for anything after 7:30AM and Before 3:30PM.  I am having a devil of a time figuring this out.  I can use datepart for hour and then again for minute, but the problem is that it filters on both individually (Hr - 1-23 and Min 1-60).  If I filter on MINUTE, I then exclude minutes 1-29 for the HOURS between 7AM and 3PM.  This doesnt work for me.

       

      Is there an easy way to do this other than setting up sets for every hour/minute combination and then exclude?  That is tedious as there will be hundreds HH:MM:SS combinations.   Please I need your help!

       

      Thanks,

       

      Dan

        • 1. Re: Hour and Minute sort
          Jonathan Drummey
          Jonathan Drummey Ambassador Oct 24, 2016 4:14 PM (in response to Dan Gordon)

          Hi Dan,

           

          There are many ways to do this, the following technique is probably the fastest assuming you are using MS Excel, a text file, or a SQL-based data source for your dateTimeField:

           

          (FLOAT([dateTimeField]) - INT([dateTimeField]))  >= .3125

          AND (FLOAT([dateTimeField]) - INT([dateTimeField])) < .64583333

           

          This will return a True/False

           

          .3125 is 7.5/24 (as in 7:30am expressed in a decimal part of a day), and .6458333 is 3:30pm expressed in a decimal part of a day.

           

          If you are using a Tableau data extract or a database view then this entire calculation could be materialized in the extract or in the view.

           

          Another calculation would be (I haven't tested this, but it looks right):

           

          (DATEPART('hour',[dateTimeField]) >= 8

            OR (DATEPART('hour',[dateTimeField]) = 7 AND DATEPART('minute',[dateTimeField]) >= 30))

          AND (DATEPART('hour',[dateTimeField]) <15

            OR (DATEPART('hour',[dateTimeField]) = 15 AND DATEPART('minute',[dateTimeField]) < 30))

           

          Finally, a third one would be

           

          (FLOAT([dateTimeField]) - INT([dateTimeField])) * 1440

           

          and then filtering it as a continuous dimension (so you get a range filter) for values between 450 and 929 (the minutes corresponding to 7:30am and 3:30pm, respectively)

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Hour and Minute sort
            Dan Gordon
            Dan Gordon Oct 25, 2016 5:57 AM (in response to Jonathan Drummey)

            Thank you for the quick reply Jonathan.

             

            I am looking at Surgical Start times in our OR.  The hours the OR are "Scheduled Open" are M,Tu,We. Fri 7:30AM to 3:30PM.  On Th the hours are 8:30AM to 3:30PM.  Of course there are cases that are added on, but I want to look at just those cases that fall with the OR Normal Operating Hours.

             

            When I place a date field in the view and make it discrete, I can format the field to Hour or to Minute, but not a combination of the two.  How do I get 7:30AM as a beginning point of a date range and 3:30PM as the end date of a data range while still looking at every calendar day over 3 years worth of data?

            • 3. Re: Hour and Minute sort
              Jonathan Drummey
              Jonathan Drummey Ambassador Oct 25, 2016 12:15 PM (in response to Dan Gordon)

              Hi Dan,

               

              There are multiple things going on in your email, I’ll try to take them apart so we can be on the same page:

               

              1) Tableau doesn’t natively understand times (it understands dates and datetimes). We can do times by normalizing all dates to a single day and then formatting that as a day, for example DATETIME(FLOAT([dateTimeField])-INT(dateTimeField])) and then formatting that to hh:mm. Otherwise the days will be in the date time and that will be a pain to deal with.

               

              2) You talk about placing a date field in the view, just to be clear are you talking about putting the field on the Filters Shelf (which is in the workspace but doesn’t define the viz level of detail) or on another field?

               

              3) Making a pull into a discrete (blue) pill tells Tableau you are interested in seeing the exact values. If you want a range filter then the pill needs to be continuous (green), and only numbers, dates, and datetime datatypes can be green pills.

               

              4) The filter criteria you specify are more complex than you’d initially stated because it’s both day of week and time of day. You wouldn’t be able to get the desired results using a continuous range filter because it can’t handle that complexity, so you’d have to use a calculation that includes the day of week. I’d use something like this:

               

              IIF(DATEPART(‘weekday’,[dateTimeField])=5,

              (FLOAT() - INT())  >= .35416667 AND (FLOAT() - INT()) < .64583333,

              (FLOAT() - INT())  >= .3125 AND (FLOAT() - INT()) < .64583333)

               

              Put this on the Filters shelf and filter for True.

               

              Jonathan