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

Question & Answer
, , , , , , ,
1

"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."
2

Hi Abhishek_24,

for native SQL I would take a code like this:

SELECT 
    VisitDate,
    PatientID,
    ROW_NUMBER() OVER (
        PARTITION BY PatientID 
        ORDER BY VisitDate
    ) AS RankAppointment
FROM 
    YourTableName;

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

[https://docs.aws.amazon.com/quicksight/latest/user/denseRank-function.html]

3

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.