Help with displaying calculation per date

[Ciara]

Hi

I was looking for some help with displaying a calculated field per date for a date range selected.

I want to be able to project the number of patients who will be admitted across a date range.

I can get it to work for one date but need help with figuring out how to change my variable depending on the date.

I have a list of current inpatients and their expected discharge date 

And I have the expected admissions with their reservation date and some have expected discharge date

I've been trying to figure out how I can add my calculated field to a table by date

based on the formula below but also based on any given date chosen (with ability to choose a date range and not just one date)

Total Current Inpatients (Any VisitID with a status of ADM IN)  

minus

Expected discharges (Any VisitID with a status of ADM IN and CurrentInpExpectedDCDate on or before the date chosen)

plus

Expected admissions (Any VisitID with a status of PRE IN and ResDate on or before the date chosen)

minus

Expected discharges of expected admissions (Any VisitID with a status of PRE IN and PreInExpectedDCDate on or before the date chosen)

Therefore if I chose today (12/04/21) I would expect my calculation to include:

total current inpatients (63)

minus

expected discharges with an expected discharge date less than or equal to 12/04/21 (4)

plus

Expected admissions with a reservation date of 12/04/21 (9)

minus

Expected discharges of expected admissions (possibly none as you wouldn't have a reservation date and an expected discharge date the same) (0)

Whereas if I chose 13/04/21 I would have

total current inpatients (63)

minus

expected discharges with an expected discharge date less than or equal to 13/04/21 (27)

plus

Expected admissions with a reservation date equal to 'today' and less than or equal to 13/04/21 (34)

minus

Expected discharges of expected admissions with a PreInExpectedDCDate less than or equal to 13/04/21 (4)

So if I were to choose the date range 12/04/21 - 13/04/21 my table would look like this

Date	Expected Occupancy
12/04/21	68 (63 - 4 + 9)
13/04/21	66 (63 - 27 +34 - 4)

I've looked into canonical date calendar but its more based on variables and the fact that depending on the date you want to display the dates you put into the variables change.

My code for one date:

Count({1<Status={'ADM IN'}>}Distinct AccountNumber)

-

Count({1<CurrentInPExpectedDCDate={">=$(=(vToday)) <=$(=$(varMaxDate))"}>}Distinct AccountNumber)

+

Count({1<ResDate={">=$(=(vToday)) <=$(=$(varMaxDate))"}>}Distinct PreInH)

-

Count({1<PreInExpectedDCDate={">=$(=(vToday)) <=$(=$(varMaxDate))"}>}Distinct PreInH)

[GaryGiles]

You need the VisitID to be associated with every Date that it might be included in your calculation.

To get the results you are looking for, you need to create an additional table in the load script that will contain a row for each date that a VisitID might be associated with.

Basically, a row for each date between the min and max of the fields ResDate, PreInExpectedDCDate, CurrentInPExpectedDCDate

Then, you can you use the new Date field as your dimension and your expressions will have access to the fields it needs.

The Count expressions will need to be rewritten, but it should be more straight forward.

You may have to make adjustments based on your data model, but here is a sample of load script that would accomplish this (assuming it is after Admissions and Discharge tables are loaded):

Join (Admissions)

LOAD

    VisitID,

    CurrentInPExpectedDCDate as CurrentInPExpectedDCDateTemp

Resident Discharge

where Status = 'ADM IN';

Join (Admissions)

Load VisitID,

      Date(RangeMin(ResDate,PreInExpectedDCDate,CurrentInPExpectedDCDateTemp)) as minDate,

      Date(RangeMax(ResDate,PreInExpectedDCDate,CurrentInPExpectedDCDateTemp)) as maxDate

Resident Admissions;

Dates:

Load VisitID,

     Date(minDate + IterNo() - 1) as newDate

Resident Admissions

While minDate + IterNo() - 1 <= maxDate;

Drop fields minDate, maxDate,CurrentInPExpectedDCDateTemp;

[GaryGiles]

Your calculation would become something like this:

count(Distinct {1<Status={'ADM IN'}>} if(newDate > ResDate,VisitID))
-count(Distinct {1<Status={'ADM IN'}>} if(newDate >= CurrentInPExpectedDCDate,VisitID))
+count(Distinct {1<PreInStatus={'PRE IN'}>} if(newDate = ResDate,VisitID))
-count(Distinct {1<PreInStatus={'PRE IN'}>} if(newDate > PreInExpectedDCDate,VisitID))

[Ciara]

Hi Gary

Really appreciate your help on this.  I've had a look at your suggestion and been playing around with it for a number of days. 

I'm struggling to make it work as I want to count any VisitID between "now" and the date chosen .... but multiple dates can and most probably will be chosen so this will change for every date.

I've thought maybe I'm going about this the wrong way ... I should forget about starting with the total amount of inpatients and try and ascertain how many patients will be still admitted on a certain date.

Therefore, using your code, for every 'newDate' I want to calculate the number of VisitID's that have a CurrentInPExpectedDCDate greater than the 'newDate' but this is coming back as 0 .

With newDate as my first column, I tried this as a measure but it just renders 0.

Count(Distinct{1<Status={'ADM IN'}>} if(CurrentInPExpectedDCDate>newDate, VisitID))

To add on possible admissions then:

Likewise if the user chooses a date in the future , I don't want to only include the expected VisitID's that have a ResDate equal to the newDate.  I want all VisitID's from 'now' up to and including the newDate.

I've written this response numerous times and I'm stuck as to how to explain myself 🙈 but I've been at this since you posted your replies and I can't get it to work! HELP hahaha I'm going mad!

Ciara

[GaryGiles]

Interesting.  I had mocked up some data and loaded it into an app using the load script I posted earlier.  I then copied and pasted your formula into the app and it produces results.

I have attached the qvf and the sample data spreadsheet if that helps.

Are we have an issue with the dates?

I did have to add some date() functions to my load script to work with 'DD/MM/YYYY', which is not my default.

[Ciara]

Hey Gary

Thanks for your continued help on this.  I really do appreciate it. 😊

I downloaded your files and ran them.  You have made both the Admissions and Discharges VisitID's matching.  (A1 to A24). The VisitID's that will be in the Discharges table will not be the same as the VisitID's that are in the Admissions table.  Once a VisitID is admitted it changes from an expected admission (PRE IN) to an actual admission (ADM IN)

Also, your calculation gives just the number of VisitID's where the CurrentInPExpectedDCDate equals the newDate.

I'm looking for the number of VisitID's that are still ADM IN that will be left on this date (so essentially any VisitID (Status ADM IN) that has a CurrentInPExpectedDCDate greater than the newDate chosen.

And then add in any expected admissions up to and including this date .... minus any expected admissions who would have been discharged up to and including this date.

[GaryGiles · Accepted solution]

Okay, I misunderstood the relationship between the tables.  Sorry about that.  I've adjusted the load script to associate newDate with both the Inpatients table and the ExpectedAdmissions table.  It should allow for that proper calculations.  Attached is qvf with the adjusted load script and the adjusted sample data.  

One issue is how to handle Expected Admissions that do not have a PreInExpectedDCDate?  If you are trying to determine if that patient should still be counted, you need to have an estimate on when they will be discharge.  Or do you always count them if they don't have a PreInExpectedDCDate?  In the load script, you will see that I calculated ResDate+7 days for missing PreInExpectedDCDate.  You can adjust that. 

Hope this helps. 

[Ciara]

Gary ..... you are AMAZING! This worked beautifully.  I feel like I need to pay you for your help 😄

Thank you SO much.

Ciara