How to create a Calculated Field for Sequential Patient Visits within a Time Frame

"We aim to generate a calculated field based on existing data with the following fields:

  1. Visit Date: Represents the date of a person’s visit
  2. Patient ID: A unique identifier for each patient…
  3. Rank Appointment: Indicates the rank of the visit (1 for the first visit, 2 for the second, and so on).

We have two parameters derived from the Visit Date: Start Date and End Date.
The goal is to create a calculated field that behaves as follows:

  • If the first visit is present within the selected time frame, display the second visit.
  • If the first visit is not present in the selected time frame, do not show the second visit."

Hi Abhishek_24,

for native SQL I would take a code like this:

        PARTITION BY PatientID 
        ORDER BY VisitDate
    ) AS RankAppointment

So for a calculated Filed in QuickSight you should use DenseRank Function


Hi @Johannes03 Thanks for the suggestion,
is there anyway i can achieve it by calculated field, as by sql it isn’t helping me what i want to get.