14 Replies Latest reply: Oct 13, 2016 5:37 PM by Steve Lord RSS

    Most current record in selected date range?

    Steve Lord

      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.

        • Re: Most current record in selected date range?
          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)

            • Re: Most current record in selected date range?
              Steve Lord

              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

                  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?
                      Steve Lord

                      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?
                  Sunny Talwar

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