For utilization, I've used two methods. For ones that span multiple days, I restructure my data using custom SQL (method 1). For ones that do not span multiple days, I've created custom calculations in Tableau (method 2).

Method 1 (duration spans multiple days):

--AGGREGATE BY HOUR BETWEEN ED ARRIVAL (userfield29) AND ED DEPARTURE (userfield30)

    ,SUM(CASE

            WHEN CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, UserField29))) <= CONVERT(int, d.[Date_ED Occupied])

                AND CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, UserField30))) >= CONVERT(int, d.[Date_ED Occupied])

                AND

                    (CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, UserField29))) < CONVERT(int, d.[Date_ED Occupied])

                        OR DATEPART(hh, UserField29)<0)

                AND

                    (DATEPART(hh, UserField30)>=0

                        OR CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, UserField30)))> CONVERT(int, d.[Date_ED Occupied]))

                THEN 1 END

        ) AS Count_ED_0000

    ,SUM(CASE

            WHEN CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, UserField29))) <= CONVERT(int, d.[Date_ED Occupied])

                AND CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, UserField30))) >= CONVERT(int, d.[Date_ED Occupied])

                AND

                    (CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, UserField29))) < CONVERT(int, d.[Date_ED Occupied])

                        OR DATEPART(hh, UserField29)<1)

                AND

                    (DATEPART(hh, UserField30)>= 1

                        OR CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, UserField30)))> CONVERT(int, d.[Date_ED Occupied]))

                THEN 1 END

        ) AS Count_ED_0100

This repeats for each hour then you have to cross-join on a source that contains every possible date. The user fields are from my original source. The [Date_ED Occupied] is from my cross-join.

Method 2 (duration does not span days):

For each point in time that I want to evaluate, I create a calculated field:

IF (float([PATIENT_IN_TIME])-int([PATIENT_IN_TIME])) <= 0.291666667

AND (float([PATIENT_OUT_TIME])-int([PATIENT_OUT_TIME])) >= 0.291666667

THEN 1 ELSE 0 END

0.291666667 = 7:00 AM

One of the outputs from Method 2:

Example output from method 1:

Hi Jonathan,

Having just solved my own encounter with this question, and because it is common scenario, I've written a blog post to explain the pattern.

RHSD - The Difference Between Lookup vs. Transactional Data in Tableau

Better to see the forest, for the trees!

Thanks!

Keith Helfrich | Twitter

Red Headed Step Data

Here I described a couple of solutions to similar scenarios:

Vizible Difference: Taking Stock with Start and End Dates

Hi Jonathan Drummey,

Thanks for creating this overview! Your post about counting from zeros has helped me and my collegues get into the right direction for an almost similar problem Counting from Nothing – A Double Remix (or, Partitioning via Table Calculations v2) | Drawing with Numbers

However, we would like to revert to level of detail functionality as you mention this should be possible as well and much less prone to error. Would it be possible for you or another bright mind on this forum to elaborate on how your original example could also be done with LOD expressions? Or point to newer but similar cases where this is already used as I am unaware of these cases after searching the forum for a while now.

Love to hear.

Hey everyone,

This issue has been stumping me for a while now.  Admittedly, my advanced analytical skills (including SQL) aren't at the level of many commenters in this thread.  With that said, has anyone been able to identify a way to calculate, for example, an hourly "census" using start date and end date that can be replicated by the less "code-savvy" users that Tableau is so well known for attracting?

Here's my particular use case:

Need an hourly census by day of week (heatmap) that spans time periods up to 2 years.  This will give users an idea of how to staff their departments.  For Acute Care units, a patient can remain in the unit for days at a time.  They need to be counted for every hour that they're in the unit up to and including the hour of discharge.  The users also need to be able to drill down into any point on the heatmap for detailed patient level data.

After researching this for a few days, I'm convinced that it can be done, but I'm also convinced that a fair portion of it may be over my head.  Since this has been an issue since Tableau's inception - have there been any advancements that will appeal to users such as myself?  If not, which of the methods in this thread will be the best suited for my case?

Thanks - this is the single number one issue holding us back from creating dashboards for a number of departments.

Is your data in Oracle by any chance? If so, I wrote a package in Oracle to do this very thing that I could share. We use it for dozens of census calculations that we run for Emergency/Inpatient/Long Term Care patients, beds and staffing levels. It can average across the hourly or day and slice by facility/unit/location group/staff type/etc...

It works quite well and would take some time for me to write up, but if you are using Oracle, it's quite a slick piece of code that runs fast that other cartesian join methods that I have used in the past.

Nope, running SQL Server connected to Meditech EMR.  Really appreciate the thought though. 

We push our Meditech data into Oracle. .. then run the census.

I will write up what I have ... at the very least, you should be able to able to write an equivalent code in SQL Server syntax that brute forces the 2 year time window you need as a single query.

Don't be scared, it will look something like this:

SELECT        s.data_source, s.inst, s.ptn_status, s.start_dt,  (SUM(LEAST(d.finish_dt,s.start_DT + 1/pwindow_resolution) - GREATEST(d.start_dt,s.start_dt))*pwindow_resolution) avg_COUNT

FROM          (SELECT * FROM  pdst_table WHERE  start_DT >= :2 AND start_DT < :3 AND data_source = pdst_data_source AND ptn_status = pdst_patient_status) s

LEFT OUTER JOIN pdst_table_TEMP d ON (s.start_DT + 1/pwindow_resolution) >= d.start_dt AND s.start_dt  <  d.finish_dt AND s.inst = d.inst AND s.data_source = d.data_source AND s.ptn_status = d.ptn_status

GROUP BY        s.data_source, s.inst, s.ptn_status, s.start_dt

HAVING (SUM(LEAST(d.finish_dt,s.start_DT + 1/pwindow_resolution) - GREATEST(d.start_dt,s.start_dt))*pwindow_resolution) IS NOT NULL

Jason, I appreciate sharing the code.  It will probably take me a while to figure out how to convert this, but I will give it a shot.  A few questions:

1. LEAST/GREATEST = MIN/MAX?
2. What is this statement saying? "start_DT >= :2 AND start_DT < :3"
3. What is "pwindow_resolution"?

I've reached out to our Meditech support team as well to see if they can help with the SQL.  If so, I'll post it here for others.

I really will write this up better some day, but for now here is a brief explanation of the core of the code. Essentially I do a self join on a small chunk of the source table with events. The keys being (1) the calculation of the average census over a given hour (no snapshot census values please!) and (2) limiting the size of the self join for speed/efficiency sake.

Oralce Code:

SELECT s.data_source, s.inst, s.ptn_status, s.start_dt, (SUM(LEAST(d.finish_dt,s.start_DT + 1/pwindow_resolution) - GREATEST(d.start_dt,s.start_dt))*pwindow_resolution) avg_COUNT

FROM (SELECT * FROM  pdst_table WHERE start_DT >= :2 AND start_DT < :3 AND data_source = pdst_data_source AND ptn_status = pdst_patient_status) s

LEFT OUTER JOIN pdst_table_TEMP d ON (s.start_DT + 1/pwindow_resolution) >= d.start_dt AND s.start_dt <  d.finish_dt AND s.inst = d.inst AND s.data_source = d.data_source AND s.ptn_status = d.ptn_status

GROUP BY s.data_source, s.inst, s.ptn_status, s.start_dt

HAVING (SUM(LEAST(d.finish_dt,s.start_DT + 1/pwindow_resolution) - GREATEST(d.start_dt,s.start_dt))*pwindow_resolution) IS NOT NULL

Brief explanation of each line:

• (SUM(LEAST(d.finish_dt,s.start_DT + 1/pwindow_resolution) - GREATEST(d.start_dt,s.start_dt))*pwindow_resolution) avg_COUNT
  This bit of code is calculates the average census over a given time window. Find the earliest ending event (either the patient left mid hour, or the end of the hour) and the latest starting event (the mid hour arrival of the patient or the end of the hour).
  pwindow_resolution defines the granularity of the time window. In my case this is usually 24 (divides the day into hours – oracle’s default data calcs result in the unit of ‘days’)
  LEAST and GREATEST are analytic functions that compare values in two different columns … a MIN/MAX only looks within a single column of data.
• (SELECT * FROM pdst_table WHERE  start_DT >= :2 AND start_DT < :3 AND data_source = pdst_data_source AND ptn_status = pdst_patient_status) s
  This bit gets the date range and dimensions from the destination table. In my case this would return the exact same fields as the very first select statement (i.e. data source, institutions, patient status, start date, and a blank avg_count (census count))
  :2 and :3 are Oracle bind variables. You can hard code these for testing purposes, but since I run this through a package, I am dynamically changing these.
• LEFT OUTER JOIN pdst_table_TEMP d ON (s.start_DT + 1/pwindow_resolution) >= d.start_dt AND s.start_dt < d.finish_dt AND s.inst = d.inst AND s.data_source = d.data_source AND s.ptn_status = d.ptn_status
  This is a self-join essentially. This is really inefficient if you run this against your whole table. In my case, we have 10’s of millions of records and doing a self-join is impractical (used to take a week, new code takes 1 hour:). Instead, I wrap this code in a loop that does a 7 day chunk at a time.
  So what I do here is I create a temporary table that contains one week of data and then do a join to that.
• GROUP BY s.data_source, s.inst, s.ptn_status, s.start_dt
  This is to roll everything up. The start_dt represents the starting point of the time window, so the time windows is from the start_dt to the next record's start_dt
• HAVING (SUM(LEAST(d.finish_dt,s.start_DT + 1/pwindow_resolution) - GREATEST(d.start_dt,s.start_dt))*pwindow_resolution) IS NOT NULL
  I believe this gets rid of the blanks so I don’t waste time joining nulls (the destination table already has zero’s filled in across the all records)

Thanks Jason!  Very helpful.  Still difficult for me to translate over to SQL, but I'm making progress. 

Jason,

I'm attempting to use Mark Jackson's code from above with my data.  I think it may work, but I'm trying to understand a few things.  For your report, do you need the patient detail available for each hour, so the user can drill in and see all patients there that hour?  This is what I need and it looks like the only way to accomplish this is to create a cartesian product between my detail data and the temporary table that stores each date.  So essentially I'm multiplying each detail row x each day in my date range.  This might be manageable, but it's quite unwieldy.  I can't think, though, of any other way to do it if i need to maintain detail. 

Just brainstorming here.  Not sure if Mark is still around.  Not able to @Mention him.