3 Replies Latest reply: May 14, 2013 10:27 AM by Nirmal Raj RSS

    expression help please

    Steve Lord

      Hi, I have a weighted average expression here that I wanted to only include most current tests.  I've been using MAX(TestDate) to get most current test in other aggr functions that do simple counts, but am having trouble putting it into a weighted average expression.

       

      SUM(aggr(SUM(TestValue)/Count(UserId), UserId, TestName, Year))/Count(DISTINCT UserId)

       

      Working from the inside out, this expression takes the average TestValue for each individual User AGGR(SUM(TestValue)/Count(UserID), UserId, TestName, Year) then totals them up and divides by the total number of users (SUM(aggr...))/Count(DISTINCT UserId)

       

      Instead of average test value for each individual user, I want it to work with the most recent test value for each individual user for a given test and year.  I've tried sticking MAX(TestDate) in a variety of places with syntax that seemed to make sense, but I am not getting sensible results yet.  I believe only the inner part, AGGR(SUM(TestValue)/Count(UserID), UserId, TestName, Year), needs to be changed.  I would ultimately like to average out the most current testvalues of the individual users.  (Instead of average out the average testvalues of the individual users.)

       

      Help!  Thanks! -Steve

       

      PS> This is in the expressions field of a straight table.  Anyone with a good idea of how to bring that function over to the script instead is welcome to post that.  I'll give correct answer to whichever one gets the job done.  (I know formula is preferable to have in script for performance reasons as well.)

        • Re: expression help please

          Hi Steve

           

          It looks like you need the firstsortedvalue() function. To get the latest value by User use -TestDate as the sort weight:

           

          SUM(aggr(firstsortedvalue(TestValue,-1*TestDate), UserId, TestName, Year))/Count(DISTINCT UserId)

           

          You are right, it will work way better in the script. You can use firstsortedvalue in the script too, I would create a separate ke yof unique Users/testname / year (if not already) and join onto it your calculation grouped by user with the first sorted value for each.

           

          EG

          something like

           

          AvgTable:

           

          Load

          Autonumber(UserId&TestName&Year) as KEY,

          UserId,

          TestName,

          Year,

          firstsortedvalue(TestValue,-1*TestDate) as latestvalue

          resident Activity_Data

          group by UserId, TestName, Year;

           

          Let me know if this helps,

           

          Regards,

           

          Erica

          • Re: expression help please

            hi al,

             

            i want to store input value to backend like qvd or something.i am getting input from excel file.