Need to calculate the count of patient, where patient that had a visit after not having a visit for X days. What transformation can work for this?

patient that had a visit after not having a visit for X days) What transformation can work for this. Idea is to get the count of patient. We have Date_Visited column, Patient_ID.
@Ramon_Lopez can you help with this…

Hi @Abhishek_24

Assuming you’re looking to filter for rows where the patient had a visit after X days, you could:

  1. Calculate difference in days from the previous visit for each patient (refer: Lag - Amazon QuickSight and dateDiff - Amazon QuickSight

  2. Apply filter on the diff in days calculated in step 1 and calculate distinct count of patient ids

Quick prototype to illustrate:
Raw data:
image

In QuickSight after adding the calculation:

Applying filter on days diff to find out visits after 40 days:

Let us know if this does the trick for you? Thanks.

1 Like