Min and max dates in the script

[Anonymous]

Hi

I need help on below problem:

Data:

Patient ID	start Date Time	End Date Time
3ta12p8	4/6/2018 6:00	4/6/2018 6:00
3ta12p8	5/6/2018 6:00	5/6/2018 6:00
3ta12p8	6/6/2018 6:00	6/6/2018 6:00
3ta12p8	7/6/2018 6:00	7/6/2018 6:00
3ta12p8	8/6/2018 6:00	8/6/2018 6:00
3ta12p8	11/7/2018 6:00	11/7/2018 6:00
3ta12p8	12/7/2018 6:00	12/7/2018 6:00
3ta12p8	13/07/2018 06:00:00	13/07/2018 06:00:00

Problem Statement: I am trying to calculate the date difference for this patient to calculate the total hospital admission days. To calculate that i am considering patient's min start date and max end date and used the formula mentioned below:

sum(aggr(max({$<[Treatment_Status]={'Completed'}>}End_Date) - (min({$<[Treatment_Status]={'Completed'}>}Start_Date)-1),Patient_ID,Hospital_Name))

This formula is working fine. but my problem is that for some patients the treatment happens multiple times and this formula will consider the min start date and max end date regardless of multiple treatments happened in different months.

I want to calculate the total hospital admission days for each patient using min start date and max end date but if the month is changed then it should calculate accordingly.

Can anyone help?

[sunny_talwar]

May be you need to add Month to your Aggr() function?

Sum(Aggr(
Max({$<[Treatment_Status] = {'Completed'}>} End_Date) -
Min({$<[Treatment_Status] = {'Completed'}>} Start_Date) - 1
, Patient_ID, Hospital_Name, MonthFieldHere))