Most current record in selected date range?

[stevelord]

Hi, II have a data table about like this to start

UserId	TestDate	BMI	BP
123	3/5/2016		1
123	4/6/2016	1
456	5/6/2016	1	2
456	7/6/2016	2	1

When the end user selects a date range, I need a formula that identifies the most current not null BMI and count the number of users for whom this value is a 1

BMI Example, I enter a vMinDate 1/1/2016 and vMaxDate 12/31/2016, the count should say 1 user has a BMI value of 1 on their most current BMI record in the date range.  But if I enter vMinDate 1/1/2016 and vMaxDate 6/1/2016, the count should say 2 users have a BMI value of 1 on their most current BMI record in the date range.

BP Example, vMinDate 1/1/2016 and vMaxDate 12/31/2016 should say 2 users have a BP of 1 on their most current BP record in the date range.  And for vMindate 1/1/2016 and vMaxDate 6/1/2016, the count should say 1 user has a BP of 1 on their most current BP record in the date range.

A standalone expression for each in a textbox or on a chart with no dimensions should suffice.

Trying to do away with a manual step where I export to excel, sort descending, remove duplicates, and countif(C:C,1) but spinning my wheels on aggr() and max() expressions.

[sunny_talwar]

Here try these:

Sum(Aggr(If(FirstSortedValue({<BMI = {"=Len(Trim(BMI)) > 0"}, TestDate = {"$(='>=' & Date(vMinDate) & '<=' & Date(vMaxDate))"}>}BMI, -TestDate) = 1, 1, 0), UserId))

Sum(Aggr(If(FirstSortedValue({<BP = {"=Len(Trim(BP)) > 0"}, TestDate = {"$(='>=' & Date(vMinDate) & '<=' & Date(vMaxDate))"}>}BP, -TestDate) = 1, 1, 0), UserId))

[johnw · Accepted solution]

Hopefully I have it in the attached. It produces the right results for the indicated test cases. The first thing I did was get rid of the "distracting" need to use variables to select the date range. I added my subroutine from here:

Subroutine to Create Data Model for From/To Date Selection

So now the date ranges are handled entirely in the data model by selecting values for FromDate and ToDate. That left me only needing to worry about the results within the selected date range. I came up with these expressions, borrowing the firstsortedvalue() code a bit.

-sum(aggr(1=firstsortedvalue({<BMI={">0"}>} BMI,-TestDate),UserId))
-sum(aggr(1=firstsortedvalue({<BP={">0"}>} BP,-TestDate),UserId))

Edit: Simplified

Edit: Pretty-fied

[johnw]

Bah, stalwar1 beat me to the basic idea of a sum(aggr()) to do it.

[sunny_talwar]

Well John - All credit to you for getting this done in half the time (just as mentioned above). I have been helping Steve for so many days.

Best,

Sunny

[stevelord]

Excellent job with this.  It hit all the correct counts for the right userids.  I also tried to break it by inserting some 2015 records and it held up.  It was also easy to see how to switch it to look at unhealthy or other kinds of values; -sum(aggr(2=.... )) for instance.  Definitely one of those situations where a million things can seem right but only one will work.

Also kudos on finding a use for calendar objects that only let you select one day on them.