topic Re: Most current record in selected date range? in QlikView

Most current record in selected date range?

stevelord — Fri, 07 Oct 2016 22:21:47 GMT

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.

Re: Most current record in selected date range?

sunny_talwar — Fri, 07 Oct 2016 22:30:44 GMT

Try these expressions:

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

Re: Most current record in selected date range?

stevelord — Fri, 07 Oct 2016 23:43:59 GMT

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

Re: Most current record in selected date range?

sunny_talwar — Sat, 08 Oct 2016 02:23:16 GMT

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)

Re: Most current record in selected date range?

stevelord — Thu, 13 Oct 2016 15:30:28 GMT

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.

Re: Most current record in selected date range?

stevelord — Thu, 13 Oct 2016 15:32:06 GMT

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

Re: Most current record in selected date range?

stevelord — Thu, 13 Oct 2016 18:01:54 GMT

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

Re: Most current record in selected date range?

sunny_talwar — Thu, 13 Oct 2016 18:03:22 GMT

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

Re: Most current record in selected date range?

stevelord — Thu, 13 Oct 2016 18:17:11 GMT

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.

Re: Most current record in selected date range?

sunny_talwar — Thu, 13 Oct 2016 18:19:21 GMT

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

Re: Most current record in selected date range?

sunny_talwar — Thu, 13 Oct 2016 18:40:13 GMT

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

Re: Most current record in selected date range?

johnw — Thu, 13 Oct 2016 19:05:23 GMT

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

Re: Most current record in selected date range?

johnw — Thu, 13 Oct 2016 19:07:13 GMT

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

Re: Most current record in selected date range?

sunny_talwar — Thu, 13 Oct 2016 19:09:43 GMT

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

Re: Most current record in selected date range?

stevelord — Thu, 13 Oct 2016 21:37:27 GMT

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.