Getting the first and last dose in a user selected date range

[bmcguire]

I have a situation where a user is going to select a date range and they want to see, for each patient, the amount of medicine for the first (oldest) dose they received in that date range and the amount of medicine for the last (most recent) dose they received in that date range. 

Patient ID	Dose Date	Dose Amount
1	1/15/2020	20
1	2/1/2020	15
1	3/1/2020	10
2	2/1/2020	10
2	2/5/2020	20
2	2/17/2020	10

So that if the user selects the dose range of 1/1/2020 to 3/1/2020 they would see something like this where the dose of patient id 1 has decreased and the dose of patient id 2 has stayed the same

Patient ID	first dose of date range	last dose of date range
1	20	10
2	10	10

I tried using aggr to determine the first dose date for a patient and set it to the eFirstDoseDate variable like this:

eFirstDoseDate = aggr(min([Dose Date]), [Patient ID])

then I made this formula. The problem that I'm running into is that it only works on some patients. For many patients the number is 0.

=if( [Dose Date] = $(eFirstDoseDate), [Dose Amount], 0))

Patient ID	Dose Date	Dose Amount	=$(eFirstDoseDate)	=if( [Dose Date] = $(eFirstDoseDate), [Dose Amount], 0)
1	1/15/2020	20	1/15/2020	0
1	2/1/2020	15	1/15/2020	0
1	3/1/2020	10	1/15/2020	0

To try to tease out some more info I changed my formula to this:

=if( [Dose Date] = $(eFirstDoseDate), [Dose Amount], $(eFirstDoseDate))

To see what the first dose date is if the dates don't match and this is what the data looked like. Somehow the if formula is returning nulls on the other dates. 

Patient ID	Dose Date	Dose Amount	=$(eFirstDoseDate)	=if( [Dose Date] = $(eFirstDoseDate), [Dose Amount],$(eFirstDoseDate))
1	1/15/2020	20	1/15/2020	-
1	2/1/2020	15	1/15/2020	1/15/2020
1	3/1/2020	10	1/15/2020	-

[Or]

Have a look at using FirstSortedValue() to get the dose amounts for the first and last dates.

Might be something along the lines of:

FirstSortedValue(Total <[Patient ID]> [Dose Amount], [Dose Date]) with the last dose being identical but with -[Dose Date] to reverse the sort order. For the first and last dates you can just use min and max.

[bmcguire]

Thank you so much for replying! This is a step in the right direction!

Now what I'm seeing is that  situations where patients got more than one dose in a day are returning null. It seems like we need to sum all doses that occurred on that day. When there are multiple doses on the same day firstsortedvalue is returning null.

In the example below patient ID 1 is returning null as the first dose because they had two doses with the same patient ID and dose date.  

so, in this situation, patient ID 1 should have a first dose of 20. 

Patient ID	Dose Date	Dose Amount
1	1/15/2020	5
1	1/15/2020	15
1	2/1/2020	15
1	3/1/2020	10
2	2/1/2020	10
2	2/5/2020	20
2	2/17/2020	10

[Or]

That is indeed a bit of a problem, since (as you already found out) FirstSortedValue doesn't break ties. You might be able to get around this by using aggr(sum([Dose Amount]),[Patient ID],[Dose Date]) but I'm not sure if that'd work or not. If you can't get around it on the front end, you could consider creating a pre-aggregated table in your script that is grouped by patient and dose date with Sum(Dose Amount) as Total_Dose_For_Date and then you can operate on that one instead of the detailed table.

[bmcguire]

Do you have any idea what is happening, from a data perspective where the if formula would return a null? The if is either true or false. Value if true and value if false. Where is the null coming from?

[Or]

Null is returned by FirstSortedValue() when there is no first sorted value (either because there are no values, or because there is more than one value to return). In your case, FirstSortedValue is trying to return the dose for Jan. 15th but it can't do that because there's more than one dose for that date.

[bmcguire]

I'm asking about null in the scenario below:

how come the if formula returns null on the 1/15/2020 and 3/1/2020 dose?

Patient ID	Dose Date	Dose Amount	=$(eFirstDoseDate)	=if( [Dose Date] = $(eFirstDoseDate), [Dose Amount],$(eFirstDoseDate))
1	1/15/2020	20	1/15/2020	-
1	2/1/2020	15	1/15/2020	1/15/2020
1	3/1/2020	10	1/15/2020	-

[Or]

I tried this on my end (date formatting changed to DD/MM/YYYY):

The second formula, which is cut off, is:

=if( [Dose Date] = aggr(min([Dose Date]), [Patient ID]), [Dose Amount],aggr(min([Dose Date]), [Patient ID]))

But I'm not sure how this helps you because this formula will only work if Dose Date is a dimension in your table, and you're trying to have one row per patient ID.

I'd also note that when the Date dimension is used, you could achieve this result without aggr():

=if( [Dose Date] = min(total <[Patient ID]>[Dose Date]), [Dose Amount],min(total <[Patient ID]>[Dose Date]))

But again, once the Date dimension is removed, this formula won't return the Dose Amount you're expecting.

[bmcguire]

I took your advice here and I added something like this to my data load script:

[Dose Per Day]:
 LOAD
[Patient ID]
, [Dose Date] as [Admin Date]
, sum([Dose Amount]) as [Daily Amount]
Resident
[Medication Admin]
Group by [Patient ID], [Dose Date];

After that I changed the formulas to 

First dose of date range:

=FirstSortedValue(Total <[Patient ID]>[Dose Per Day.Daily Amount], [Dose Per Day.Admin Date])

Each patient has the same oldest dose (regardless of what patient that does was for)  and the same newest dose (regardless of what patient that does is for) for the last dose. 

Patient ID	first dose of date range	last dose of date range
1	20	10
2	20	10

Then if I add the first dose date it removes the First Dose Amount for some of the patients

Patient ID	first dose of date range	last dose of date range	=aggr(min([Dose Per Day.Admin Date]), [Patient ID])
1	-	10	1/15/2020
2	-	10	2/1/2020

If I filter to just one patient the numbers are correct.

[Or]

Wouldn't you be able to get the first and last dose dates with a simple min() and max() aggregation, rather than a nested one?