I’m seeking assistance with setting up a third table chart in QuickSight to identify patients with appointments only within specific date ranges. Here’s the scenario and requirements:
Dataset Fields:
Start Date (Appointment Date)
Patient Account Number
Patient Name
Current Setup:
I currently have two table charts:
Left Chart: Filtered by Start Date, displaying appointments during the specific period.
Right Chart: Filtered by Start Date , displaying appointments during the specific period.
Objective:
I aim to create a third table chart to show the difference between the first two charts, specifically displaying patients who appear in the first chart but not in the second chart.
Approach:
Logic for Comparison: The comparison should be based on patient names between the two date ranges to identify the difference.
Calculation or Logic: I’m looking for a method to achieve this, either through creating a calculated field or applying a logical condition within QuickSight.
Single Tab Setup: To enhance user control over date ranges, I’d like to incorporate a date control feature where users can select their desired date ranges (e.g., a week, 15 days, a month, etc.).
Adjustment Needed:
I want the flexibility to change the date ranges in both charts without restrictions.
Hello @Deepshikha1, I believe you could accomplish this by using lastValue calculations to retrieve a name for users in each category. This would also require your start date and end date filters to be linked to parameters. You would need to use 2 calculated fields to only return Patients within the 2 date ranges. Then based on those return values, run the lastValue calculation on both to compare them.
date1Patients = ifelse({start_time} >= {start date 1} AND {start_time} <= {end date 1}, {patient_control_num}, NULL)
date2Patients = ifelse({start_time} >= {start date 2} AND {start_time} <= {end date 2}, {patient_control_num}, NULL)
I believe something like this would work. You may even be able to do something like distinctCountOver based on the first 2 calculated fields and check if that number is greater than 0 to determine if a user was present. Let me know if this helps!
Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!