For week 10 of #WOW2024, Yoshitaka Arakawa launched the challenge of designing a dashboard that shows the number of patients waiting in 30-minute intervals. This challenge will test your skills in managing date-times and using data densification techniques.
The key requirements of this challenge are:
- Allow the selection of the quarter to consult.
- Create a bar chart that displays the number of patients waiting during each 30-minute interval in a 24-hour period.
- When selecting a bar, i.e. a 30-minute interval, it should be highlighted with a different color.
- Create a chart showing the list of waiting patients within the selected interval.
This challenge uses a custom dataset expanded from available healthcare and emergency data from the Real-World Fake Data (#RWFD) initiative and a dummy dataset for densification. You can download them from here.
Why will we require data densification?
Data densification is necessary to generate the bar chart that shows the number of patients waiting in 30-minute intervals. Since there is a record of data for each patient visit, it is essential to duplicate patient information based on their waiting period. This means counting the patient in each of the intervals/bars that their waiting schedule involves. To better understand this concept, let’s consider two patient examples, both with a 20-minute waiting period.
In the first case, the patient should only be counted in a single time interval, since said interval covers the entire schedule. On the other hand, in the second case, the patient must be counted in two-time intervals or in two bars of the graph, since their waiting period impacts two different intervals.
To ensure this accurate representation, a relational model will be implemented where each patient record is related to as many records in the dummy data table as necessary. This will allow each patient to be counted as many times as required in the 30-minute waiting intervals, depending on their specific waiting schedule.
Below I describe the steps I followed in my latest version.
STEP 1. DATA DENSIFICATION.
The two data tables will be related to the following definition:
It is important to note that in the definition of the relationship a calculation is used in the “ER Hospital” table, where the resulting value must be greater than or equal to the index value in the dummy table. The formula used for this calculation is the following:
| INT(([Patient Waittime] + DATEPART(“minute”, [Date Start]) – IIF(DATEPART(“minute”, [Date Start]) >= 30, 30, 0)) / 30) |
This formula basically adds the patient’s total waiting minutes to the minutes elapsed from the start of the first interval in which the patient is counted to the minute the patient arrives in the waiting room. By dividing this result by 30, we obtain the maximum value of the “index” with which we will relate both tables for each patient. This way, each patient’s record will be related to as many records in the dummy data table as necessary based on their specific wait time.
STEP 2. DEFINE PARAMETER.
We will define a parameter to identify the 30-minute interval selected by the user.
STEP 3. CALCULATE STANDARDIZED DATE-TIME FIELDS.
The first definition refers to an intermediate field or calculation that will be used in other subsequent calculations. This calculation will be determined from the value of the “Date Start” field and the result of multiplying 30 minutes by the “Index” value of the densification “dummy” table.
| Date Start with Index | [Date Start] + ([Index] * 0.02083333) // 0.02083333 = 30 minutes // 30 minutes / (24 hours * 60 minutes) = 0.02083333 |
Subsequently, the field that we will use for the “x” axis will be calculated, with the objective of standardizing the date-times calculated in the previous step. To do this, the hours and minutes information will be taken from each value obtained in the previous calculation, truncating the minutes at the beginning of the corresponding interval.
The purpose is for the new date-time fields to use a standardized day (in this case, “1/1/1900”) and for the minutes to be wrapped/truncated at 30-minute intervals.
| Axis | DATEPARSE(“hh:mm”, STR(DATEPART(“hour”, [Date Start with Index])) + “:” + STR(INT(DATEPART(“minute”, [Date Start with Index]) / 30) * 30)) // The formula allows me to round every 30 minutes // And standardize all days to the same day at 1/1/1900 |
Then the field that will indicate the end of each interval will be calculated.
| Label – Axis End | [Axis] + 0.02083333 // 0.02083333 = 30 minutes // 30 minutes / (24 hours * 60 minutes) = 0.02083333 |
STEP 4. DEFINE THE QUARTER FIELD.
| Quarter | DATETRUNC(“quarter”, [Date Start with Index]) |
It is important to note that in the formula we will use the “Date Start with Index” field instead of “Date Start”. This choice will ensure that cases in which a patient begins their wait in one quarter but ends their waiting time in the following quarter are considered in both periods.
STEP 5. DEFINE THE FIELD THAT INDICATES US IF THE 30 MINUTE INTERVAL IS SELECTED.
| Is Selected? | [Axis] = [Parameter] |
STEP 6. DEFINE THE FIELD TO SIMULATE A SHADING EVERY 4 COLUMNS.
In this step, we will use the “INDEX” function to determine the number of the column being displayed and set a value of one for the first 4 columns, followed by a value of zero for the next 4 columns, and so on.
| Column Banding | ((INDEX() – 1) / 4 + 1) % 2 // The technique consists of alternating shading in blocks of 4 columns, // where the first 4 columns are set to 1 and the next 4 to 0, // repeating this pattern successively. |
STEP 7. CREATE THE BAR CHART.
Move the “Quarter” field to the filter shelf and set a single selection visible filter and customize it to not show the “All” option. Select the value “2020 Q1”.
Move the “Axis” field to the column shelf and select the “Exact Date” option.
Move the “Column Banding” field to the row shelf. Change to bar chart type. Change to color “#f5f5f5” and use maximum manual size. Delete the wording from the toolips.
Capture the following formula directly in the row shelf: “COUNTD([Patient Id]) “.
On the second axis, add the “Is Selected” field to the color shelf and adjust the colors. And the “Axis” and “Label – Axis End” fields to the tooltips shelf and configure the tooltips.
Set up a dual axis and hide both axes:
STEP 8. CREATE PATIENT DETAIL CHART FOR THE SELECTED INTERVAL.
Move the “Is Selected” field to the filter shelf and select the value “True.” Next, go back to the first chart and set the “Quarter” filter so that it is also applied on the new chart. Then, go back to the second chart and place the following fields in the line shelf: “Department Referral”, “Patient Id”, “Date Start”, and “Date End”. Sort the “Department Referral” field in descending order by number of patients and the “Patient Id” field in descending order by wait time. Also, make sure that both date fields use exact date and discrete value.
Move the “SUM([Patient Waittime])” field to the color shelf and use the gray color palette.
To customize the chart title, first move the “Axis” and “Label – Axis End” fields to the detail shelf and make sure the two date fields use exact date. Capture the “SIZE()” formula directly in the detail rack.
Customize the tooltips:
STEP 9. DEFINE PARAMETER ACTIONS.
In the dashboard where the two graphs created in the previous steps are included, add a parameter action:
| PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
| Parameter | Char1 | Parameter | ATTR(Axis) | None |
Finished!
Link here.
Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter ( @rosariogaunag ).
RosarioViz 