Length of Service Days

[salleninsd]

Hello, I am struggling to get a couple of calculations out of my model.  We have customers that have an Admission Date and two different Discharge dates (1 is Live Discharge and 1 is Death).  I want to get to Avg Number of Days across filters.  My assumption is to do this calculation with NetWorkDays for both of these (Live/Death), and calculate them both together so there is one Length of Service Days number.

I've attached a couple of images so you can see the data fields.  Should I do this in the Load or Set Analysis?

Any help you can provide is most appreciated!

Scott

Partial ModelStatus NamesDate Key

[salleninsd]

Oh, I probably need to run this by "customer number"...  PATIENT_PK

[salleninsd]

And also... if there is not Live Discharge or Death date, it should be through Today()-2, because the model gets refreshed every day, but through two days prior to today.

[vinieme12]

can you post some dummy data in excel?

[salleninsd]

Thank you for checking into this, Vineeth.  Will this information be sufficient, it's just a flat file with dummy data, but I think it has the key fields.

Thank you in advance for your help!

[vinieme12 · Accepted solution]

try 

=avg(aggr(max({<CENSUS_ACTIVITY_STATUS_NAME={'Deaths'}>}FullDate)-max({<CENSUS_ACTIVITY_STATUS_NAME={'Admissions'}>}FullDate),PATIENT_PK))

OR with networkdays()

avg(aggr(NetWorkDays(
max({<CENSUS_ACTIVITY_STATUS_NAME={'Admissions'}>}FullDate),max({<CENSUS_ACTIVITY_STATUS_NAME={'Deaths'}>}FullDate))
,PATIENT_PK))

[salleninsd]

Thank you so much for your quick response!  I will try these solutions tomorrow and see if they work for me and let you know.  I really appreciate your help!  Scott 

[salleninsd]

Hi Vineeth!  I just wanted to let you know that this worked for me.  I discovered that I need to put in an identifier to separate periods of time where a customer was admitted more than 1 time, so the max functions only cover specific cases.  Right now, it's driven off of a customer number that does not change.

Again, I appreciate all your help!  Have a good night.

Scott