Create a chart for Patient Account Number to show the difference of other two charts

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:

  1. Left Chart: Filtered by Start Date, displaying appointments during the specific period.
  2. 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:

  1. Logic for Comparison: The comparison should be based on patient names between the two date ranges to identify the difference.
  2. 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.
  3. 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.

1 Like

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)

patientsNotInBoth =

ifelse(
lastValue(
    [{date1Patients}],
    [{start_date} ASC],
    [
        {patient_control_num}
    ]
) =
lastValue(
    [{date2Patients}],
    [{start_date} ASC],
    [
        {patient_control_num}
    ]
),    'Yes',
'No'
)

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!