1 Reply Latest reply: Jul 22, 2013 1:59 PM by Steve Lord RSS

    Firstsortedvalue combine with formula that has min/max date variables

    Steve Lord

      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!