You can try the attached workbook that I did a while back. See if the screenshot below matches what you're trying to do? Or use this thread: Calculate No. of Patient Admitted and Discharged on each Day by Admission Date and Discharged Date Use the last sheet as reference. If it answers your question, please mark this response as correct. Thx, Don
Is your table keeping track of the total # of patient's the are present at any given hour? I'm not sure that it does because there are a lot of blank cells.
I think what you're trying to get to might be difficult...just the same I found the following thread which helped point me in a potential direction. It plays out really well for using a day level of detail (I've included a screenshot but not the workbook for that effort). Just the same I tried going to 'hour' level of detail and the attached workbook for that is what I got. Here's the TB reference link: https://kb.tableau.com/articles/howto/showing-records-that-fall-within-a-period-of-time
This effort requires multiple nested table calculations to get to what you might be looking for. If anything it should point you towards some additional ideas...
Using 'day' to display number of patients active across multiple dates, using ED entries only.
Using 'hour' to display number of patients across multiple hours, using ED entries only. Filters for 'weekday' and mm/dd/yyyy.
The downside to this effort is that I can't verify the actual underlying data (Patient A is part of hour 1 and hour 2 and hour 3 etc.), likely due to the number of nested table calc's involved. You'd need to verify against your own data whether it works or not.
Regardless, hope it helps...thx, Don
That is exactly the reference that I'm using Don! I'm in the middle of trying to see if it works with my data now. I'll be happy if I can get my "hour" sheet to look like yours and the reference. I like how you used the filters for day, month, and year so I will try to replicate that also.
If I can verify that it works with my data the next step will be convert that line graph into a highlight table so we can look at the hourly census over a range of dates quickly but I guess one thing at a time. I'll let you know how it turns out.
I didn't realize this would be so complicated!
So it actually worked, Don!! Here is a pic of the completed line graph. I did not yet add in your filters - just picked a range of dates in Sept. Here is the result:
You can see that mid-day is the ED's highest volume. Notice how the date increases in hour increments going horizontally. Next step is to make a highlight table. What I would like to do is have the days of the week running along the Y axis and 1 days' worth of data (hours 0-23_ running along the Y axis. Since this data spans several days the x axis is very long and does not come back to the beginning of line graph each day - it is just one long range of continuous hours for 1 entire week, which is not particularly helpful. My guess is that making the table I am envisioning would have something to do with the formula in the Active Number of Patient's field - or maybe the start date field- and when I click on edit calculation there is a field that asks how often you want to re-calculate but it is either greyed out or recalculate by "day" is not a choice.
I was able to make a highlight table of all the data in one long row but cant seem to the table I want from this. What do you think? Any ideas?
I wish I could send you my data but unfortunately I cannot.
Glad you were able to make it through all of those nested table calculations; yes, they can be complex!
I know what you want to achieve in terms of view but I don't think you're going to get there as I tried with my set of data. The closest I could get to what you're looking for was to apply those (previous post) filters which would at least bring the view down to a more manageable level.
You can certainly try to bring in the Arrival Date on Rows using Arrival and setting the date to Weekday and give that a shot. You'll likely end up with 7 individual line graphs for each day of the week; but then you'll need to further refine your view to filter down to a week's set of data...hard to tell because your set of data is somewhat different from mine. Keep experimenting tho!
Lisa and Don,
Glad you got it worked out.
Late to the conversation, but just wanted to throw out some other
takes and discussions of this general type of problem:
Thank you Swaroop!