Min and max dates in the script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Min and max dates in the script

sunny_talwar — Thu, 29 Nov 2018 13:19:31 GMT

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))

topic Re: Min and max dates in the script in QlikView

Min and max dates in the script

Re: Min and max dates in the script