topic Average Function in QlikView

Average Function

Wed, 05 Sep 2012 17:18:11 GMT

I currently have an expression taking the sum of all weeks in the set "WeekofYear". I want to rewrite this expression to take the average of all weeks in the quarter except the most current week which is defined by the variable $(vMaxWeek).

Avg

({<[Source Type]={'RACE_REV_EST'}, [WeekOfYear] >}NAT_AMT) / $(vScaleM)

Re: Average Function

Jason_Michaelides — Wed, 05 Sep 2012 17:24:36 GMT

Try:

Avg

({<[Source Type]={'RACE_REV_EST'}, [WeekOfYear] ={"<>$(vMaxWeek)"} >}-{<>} NAT_AMT) / $(vScaleM)

Hope this helps,

Jason

Re: Average Function

Wed, 05 Sep 2012 17:38:03 GMT

Hi Jason,

Thanks for your help. I tried your expression but it's not producing a number. QlikView is having problems with the last part of the expression.

{<>} NAT_AMT) /

$(vScaleM)

Re: Average Function

Jason_Michaelides — Wed, 05 Sep 2012 17:39:14 GMT

Oops! Sorry - I meant to remove that...

Avg

({<[Source Type]={'RACE_REV_EST'}, [WeekOfYear] ={"<>$(vMaxWeek)"} >} NAT_AMT) / $(vScaleM)

Re: Average Function

Wed, 05 Sep 2012 17:46:19 GMT

Still no cigar. What if we tried to produce the average without trying to subtract out the most current week ($(vMaxWeek)) from the calculation. Would that be easier to write?

Re: Average Function

Jason_Michaelides — Wed, 05 Sep 2012 18:04:43 GMT

Can you post your app please? Would be much easier to help then.

Re: Average Function

whiteline — Wed, 05 Sep 2012 18:12:20 GMT

Avg({<[Source Type]={"RACE_REV_EST"}, [WeekOfYear] ={"=WeekOfYear<>$(vMaxWeek)"} >} NAT_AMT) / $(vScaleM)

And the syntax $(vMaxWeek) depends on what is stored in this variable (expression with or without =).