expression help please

[stevelord]

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

[#2 · Accepted solution]

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

[stevelord]

Thanks, my boss says this worked for him when he tried it in one of his scripts.  We were ambushed by other tasks and I didn't get to test it myself, but my boss is happy with it so correct answer to you.

[#4]

hi al,

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