I have a dataset full of patient data about hospital admissions and I’m trying to calculate the number of days between the previous discharge date and the current admission date. For the purpose of this question, the relevant fields are:
PatientID
AdmitDate
DischargeDate
For a given date range and a given row, I want to be able to identify the discharge date of the previous admission (null if there wasn’t one) then add a calculated field that contains that discharge date to the current row.
From there, I understand how to look at the date difference and determine whether a patient was readmitted within 30 days. But I’m having a hard time getting to the prior discharge date. The closest example I found was in this discussion from 2023:
The problem with this method is that it is comparing dates from the same column. I need to compare the AdmitDate and the DischargeDate. I’ve used the rank function to number the dates for both AdmitDate and DischargeDate. For each row, I want to add the DischargeDate from the previous AdmitDate for the same PatientID as a new column value, PriorDischargeDate.
Hello @Ross_Martin, welcome to the QuickSight community!
You might want to utilize the lastValue calculation and partition by the PatientID and AdmitDate to pull the last discharge date for each. I’ll link the documentation below and provide an example.
Thanks, @DylanM. I’m not sure this will work. In any case, I’m not able to get any of these partitioned calculations to work in QuickSight. The error I get is:
As a new user here, I apparently can only upload one image per post…
Hello @Ross_Martin, when you receive that error, it means that your visual requires one or more of the fields you are referencing in your calculated field within your visual. If you are using a table, start adding some of the fields you are referencing to see if you can get the values to appear. Once completed, you can figure out which fields you are able to remove and others you can hide from the table. Let me know if this helps!
Unless I don’t understand the logic of lastValue, I don’t think this result is what I’m after. In the above table, wouldn’t that just return 1/17/2024 for Patient 1 (the last discharge date for that patient)?
What I’m trying to capture is the gap between the prior admission’s discharge and the current admission’s admit date:
For any given time range, there could be multiple admissions for a single patient, so I have to identify the prior admission (looking back one admission) for every row of admission data.
I was able to get the visual to work by including referenced fields as you suggested, but needed to do that in the “group by” section of the table.
Also, I was ultimately successful in returning the value of the prior discharge date by using a lag function:
Of course, I’m having additional challenges (nothing is ever easy!) in calculating the number of days between these two dates. I’ll write about that in a separate message.
Hello @Ross_Martin, does the lastValue calculation not provide the most recent discharge date for each patient ID? That can be utilized to create the logic to get the difference between that and the next admit date. The lag function should work as well since you are partitioning by the patient ID.
Now, I am sure the issue you are referencing is an aggregation issue when you try to subtract the previous discharge date from the current admission date. Here, we may need to run a min calculation against the admit date. I believe this will resolve the error.
Hello @Ross_Martin, since we have not heard back from you with any follow-up information, I will mark my above response as the solution. Please let me know if you have any remaining questions on this topic, and I can guide you further. Thank you!
@DylanM – So sorry for the radio silence. Had to move to other client work. Just for an update:
The final issue (ha!) I’m having is that I can’t calculate the days between the prior discharge date and the admit date. That’s because one is a partitioned value and one is not. One workaround for this issue is to use a partitioning calculation like max on the admit date value: -dateDiff(max({admit_date}),{prior_discharge_date_})
This will only work for the most recent admission. After that, it calculates the wrong gap.
Thank you for your help on this issue. When we reopen the use case, I’ll start a new thread if I need help again.
