Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stevelord
Specialist
Specialist

Most current record in selected date range?

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

 

UserIdTestDateBMIBP
1233/5/20161
1234/6/20161
4565/6/201612
4567/6/201621

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.

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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

View solution in original post

14 Replies
sunny_talwar

Try these expressions:

BP

=Count(DISTINCT {<BP = {$(=FirstSortedValue({<BP = {"=Len(Trim(BP)) > 0"}, TestDate = {"$(='>=' & Date(vMinDate) & '<=' & Date(vMaxDate))"}>}BP, -TestDate))}>} UserId)

BMI

=Count(DISTINCT {<BMI = {$(=FirstSortedValue({<BMI = {"=Len(Trim(BMI)) > 0"}, TestDate = {"$(='>=' & Date(vMinDate) & '<=' & Date(vMaxDate))"}>}BMI, -TestDate))}>} UserId)

stevelord
Specialist
Specialist
Author

Thanks, but that is behaving oddly.  For instance, when I enter 1/1/2016 vMinDate and 5/1/2016 vMaxDate I should get a count of 1 in both boxes, but it gives a count of 2 in both boxes.

Also, your formulas look like they are geared toward counting the number of users with a BMI or BP value, and I'm trying to count number of users with a BMI value of 1 as their most current BMI.  (Isolating BMI/BP that weren't blank was step one, identifying the most current BMI/BP was step 2, and counting if that most current value = 1 was step 3. )

sunny_talwar

Can you check if these expressions gives you what you wanted?

=Count(DISTINCT {<BP = {$(=FirstSortedValue({<BP = {"=Len(Trim(BP)) > 0"}, TestDate = {"$(='>=' & Date(vMinDate) & '<=' & Date(vMaxDate))"}>}BP, -TestDate))}, TestDate = {"$(='>=' & Date(vMinDate) & '<=' & Date(vMaxDate))"}>} UserId)

=Count(DISTINCT {<BMI = {$(=FirstSortedValue({<BMI = {"=Len(Trim(BMI)) > 0"}, TestDate = {"$(='>=' & Date(vMinDate) & '<=' & Date(vMaxDate))"}>}BMI, -TestDate))}, TestDate = {"$(='>=' & Date(vMinDate) & '<=' & Date(vMaxDate))"}>} UserId)

stevelord
Specialist
Specialist
Author

Hi Sunny, this is extremely close now.  I setup a little straight table with users for a dimension and your expressions for BMI/BP count to test.  Below are some notes.  The counts hit desired results for all but two date range scenarios I tried, and when I setup the straight table I saw there were offsetting errors on a few other date range scenarios.

I tried adjusting logic various ways to count people only if their most current non-blank value was a 1, but mostly ran into errors about nested aggregation not allowed.

I'm going to go back into the script and start by loading only the nonblank values for BMI on one table, and the same for BP on another table, so we could remove that layer of complexity from the front end expression.  Then maybe if I replace the part about lenBMI>0 with BMI=1 I will be closer.

stevelord
Specialist
Specialist
Author

Notes I allude to are in the attached qvw. They're below the little straight table I made.

stevelord
Specialist
Specialist
Author

Hi Sunny, thanks for all your help with this maddening logic problem.   I've asked my colleagues to go ahead and provide me a list of the clients and date ranges so I can just make some hard-coded tables.  I will then join this table onto the biometric records, and load only records where testdate>=mindate and testdate<=maxdate associated with any given record.  Separately, I will also isolate the max(testdate) for each user's BMI and join that back onto the first table.  Then I'd store and reload the records from those tables only where testdate=maxtestdate.  After that, it should be a breeze for me to have tables or pie charts or whatever tally up the users based on their bmi values.

While end users might not be able to control the date ranges directly inside the QV dashboard, I can let those date ranges exist on an excel spreadsheet the dashboard references, that they can update.  Then I'd reload the dashboard for them (or take another look at the feature to allow end users to reload a dashboard).

sunny_talwar

So, I should not be spending more time on this?

stevelord
Specialist
Specialist
Author

It would be a great one to solve if you can.  If you do solve it, I will definitely test/verify and use it.  I burned myself out trying various angles and worried it might be unsolvable within a single expression.  If you feel an answer is in your grasp though, by all means let me know.  You might make a contest with the other level-11 people to see if it's well and truly unsolvable.

sunny_talwar

I know few people who might be able to solve this in half the time or may be less. One such person is Stefan, but I am not sure if they would have time to look at this. I will keep trying and will let you know if I find anything