Hello QuickSight Community,
I need assistance with creating a third table chart in QuickSight to identify patients with appointments only in specific date ranges. Here’s the scenario:
Dataset Fields:
Start Date (Appointment Date)
Patient Account Number
Patient Name
Current Setup:
I have two table charts:
Left Chart: Filtered by Start Date (Jan 1 - Feb 29, 2024), displaying appointments during this period.
Right Chart: Filtered by Start Date (March 1 - March 31, 2024), displaying appointments during this period.
Objective:
Create a third table chart to show the difference between the first two charts. In other words, I want to display patients who appear in the first chart but not in the second chart.
Approach:
Logic for Comparison: We need to compare the patient names between the two date ranges to identify the difference.
Calculation or Logic: Can we achieve this by creating a calculated field or applying a logical condition within QuickSight.
Single Tab Setup: To facilitate user control over date ranges, incorporate a date control feature where users can select their desired date ranges (e.g., a week, 15 days, a month, etc.).
Assistance Needed:
Guidance on whether a calculated field is necessary or any alternative logic to achieve the objective.
Instructions on how to implement this in a single tab, incorporating a date filter or control for user flexibility in selecting date ranges. @DylanM
Any insights or suggestions on the above points would be greatly appreciated. Thank you!
Could we explore another approach, perhaps using a calculated field? The steps mentioned—creating two filtered datasets and joining on Patient name, followed by creating a select statement—how does the first approach contribute to achieving my objective?
How about creating a dataset from a SQL query like:
WITH
set_a as (
SELECT patient_account_number
FROM table_name
WHERE date >= timestamp '2024-01-01' and
date < timestamp '2024-02-29'
),
set_b as (
SELECT patient_account_number
FROM table_name
WHERE date >= timestamp '2024-03-01' and
date < timestamp '2024-03-31'
)
SELECT
patient_account_number
FROM set_a
WHERE patient_account_number NOT IN (SELECT patient_account_number FROM set_b)
The limitation is that you cannot use the same table view for all three tables, but you can use multiple datasets in the same dashboard.
I understand you would like the functionality to allow adjusting the date ranges?
This could be easily achieved by expanding the date range in the example SQL query (Eg, for the whole of 2024). You can then add a filter to the visual, and pin filter Controls to the sheet.
This will allow the viewer (user) to more dynamically adjust the dates required, providing they are within the date range selected in the SQL query.
Additionally, you could amend the SQL query date range to use a relative date period, such as an equivalent to ‘Year to Date’.
Of course, if the dataset is relatively small, you may choose to skip the date range in the SQL query.
Reply if you need help amending to a relative date range
