Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

stevelord
Valued Contributor

Firstsortedvalue combine with formula that has min/max date variables

Hi I have this formula on a straight table that works nicely with a couple of input boxes for mindate and maxdate.  And now I'd like to add a function to pick only the most current value for a given user within that date range.  I have a formula for that too and am trying to combine them basically.

The straight chart has dimensions: ClientAccountName, UserId, and TestDate

The chart's formula that works with my mindate maxdate input boxes is:

only(if(TestDate>=MinDate and TestDate<=MaxDate, rbBMI))

(rbBMI is a field I created in script that basically gives a low, normal, and high message depending on the person's BMI field value.)

I have this formula to select most current record for a user within the year that is selected on a listbox, and I want it to work within the formula to pick the most current record for a user within the date range selected on the input boxes.

firstsortedvalue(rbBMI,-(TestDate*100000+BMI))

I gave the testdate a 100,000 weight so as to keep a testvalue from overriding the date value, and I put a testvalue in there to help it go after the non-zero test if there happens to be an empty value for the test on the same date.  (Someone may have refreshed all fields when adding a value to only one field and caused 0 values to be uploaded for the other fields... or something.  It's pretty odd, but somewhere out beyond my control.)

So I tried nesting one equation in the other and visa versa, but it's not working.  If anyone can get me something to pick the most current testvalue within a mindate/maxdate variable range specified by input boxes, without hardcoding date ranges into script, I'd be grateful.  Thanks!

1 Reply
stevelord
Valued Contributor

Re: Firstsortedvalue combine with formula that has min/max date variables

Forgot to mention, my firstsortedvalue formula is being used on a different pivot table that has the same dimensions: ClientAccountName, UserId, and TestDate.  It works fine there and should work the same over on a straight table, though I can flip the straight table to a pivot table if needbe.

Community Browser