1 of 1 people found this helpful
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)
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?
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:
(FLOAT() - INT()) >= .35416667 AND (FLOAT() - INT()) < .64583333,
(FLOAT() - INT()) >= .3125 AND (FLOAT() - INT()) < .64583333)
Put this on the Filters shelf and filter for True.