Your reply seems to correct the arrival date for those arriving after midnight but I can't seem to get a formula that will correctly identify the patient's that come in after 9pm.
Here's what I wrote:
//defines 9p-7a shift
AND DATEPART('hour', [Arrival]) >=21 and
DATEPART('hour', [Arrival Date (for night shift)]) <7
THEN "9p-7a" END
All I seem to be getting are null values.
1 of 1 people found this helpful
We resolved this exact issue for dealing with Firefighters who work a 24-hour Shift and have to satisfy the issue of working into the next 'day' and capturing the number of responses they went on by using the following calc:
// 24-hour Firefighter Shift
// For the first shift segment (hours 0700-0000), keep the Date as is
// For second shift segment crossing over midnight (hours 0000-0659), set the Date to the next day from 0-7
// From Hours 0700-0000
IF datepart('hour', [Response Date]) >=7 and datepart('hour', [Response Date]) <=23 then [Response Date]
// From Hours 0000 to 0700
ELSEIF datepart('hour', [Response Date]) >=0 and datepart('hour', [Response Date]) <7 then
dateadd('day', 1, [Response Date]) END
So you're close to achieving it...hope this works for you. Thx, Don
I really do think it's really close. I don't understand why I would have "1" in the dateadd part of the calc instead of "-1" since I want the people who come in between midnight and 7am to be counted with the previous days census.
I'm also having some trouble getting the results labeled as "9p-7a"
Here's what I have :
AND DATEPART('hour', [Arrival]) >=21
AND DATEPART('hour', [Arrival]) <=23 then "9p-7a"
ELSEIF DATEPART('hour', [Arrival]) >=0 and
DATEPART('hour', [Arrival]) <7 then dateadd('day', -1,[Arrival]) THEN "9a-7p" END
Tableau doesn't seem to like that last THEN. I am getting a syntax error - I guess because I have 2 then statements in a row, which I know doesn't make sense but.how do I label the results as "9a-7p" so I can use them in a filter or parameter??
Yes, the DateAdd of "1" will look for any relevant data in the next day for the early morning time period involved.
If using '-1' then you're getting data from the early morning of the 13th (0000-0700) and not the 15th...if the 24-hour period involves the 14th starting at 0700 hours, per your example.
Please see newly attached sample workbook which emulates what you're trying to do? Below is the screenshot of the example and the calculation used. I did two things:
(1) Created a Shift Period using 'arrival time' from some sample data I had to set the entire 24-hour period from 0700-0659. That part might not have been necessary but in order to truly capture the data I'd rather use it to get the data from the 0000-0700 period properly.
(2) Using that calculation, then created a Weekday Shift calc which can then be used as a filter or to color etc. by individual and segmented shift periods for all three.
Notice that the shift from 9p-7a is segmented correctly into the next date. Meaning, your counts and display/viz will also be more accurate.
Hope this helps you along. If yes, please mark as correct so that others may find it useful in the future. Thx, Don
Thanks so much Don!! That is exactly what I have been trying to do!
Glad to have been able to help!