topic Re: expression help please in QlikView

expression help please

stevelord — Wed, 08 May 2013 19:43:51 GMT

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

Wed, 08 May 2013 22:02:08 GMT

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.

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

stevelord — Tue, 14 May 2013 14:23:08 GMT

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.

Re: expression help please

Tue, 14 May 2013 14:27:06 GMT

hi al,

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