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