Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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
Bah, stalwar1 beat me to the basic idea of a sum(aggr()) to do it.
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
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.