topic Where to insert aR = {'aR'} into Average aR expression ? in QlikView

Where to insert aR = {'aR'} into Average aR expression ?

paulyeo11 — Wed, 03 Aug 2016 02:21:31 GMT

Hi All

Sunny give me the below expression work fine , it return 5,234,000 at Table 1 is correct :-

Aggr(If(YearMonth = Max(TOTAL YearMonth),

RangeAvg(Below(RangeSum(

Above(RangeSum(Above(Sum({<year, month>}[Amount]*-1), 0, RowNo()))),

-Sum(TOTAL Aggr(Sum({<year, month>}[Amount]*-1), YearMonth))), 0, Max(TOTAL {<year = {$(=Max(year))}>} month)))), (YearMonth, (NUMERIC, desc)))

I need to insert aR = {'aR'} into above expression , i try as below expression at table 2 :-

Aggr(If(YearMonth = Max(TOTAL YearMonth),

RangeAvg(Below(RangeSum(

Above(RangeSum(Above(Sum({<year, month,aR = {'aR'}>}[Amount]*-1), 0, RowNo()))),

-Sum(TOTAL Aggr(Sum({<year, month,aR = {'aR'}>}[Amount]*-1), YearMonth))), 0, Max(TOTAL {<year = {$(=Max(year))}>} month)))), (YearMonth, (NUMERIC, desc)))

It return wrong value 4,927,000 , where did i go wrong ? ( Remark when not select filter aR it give wrong value)

Paul

Re: Where to insert aR = {'aR'} into Average aR expression ?

paulyeo11 — Wed, 03 Aug 2016 02:24:45 GMT

my qvf

Re: Where to insert aR = {'aR'} into Average aR expression ?

sunny_talwar — Wed, 03 Aug 2016 02:54:36 GMT

Try this:

Aggr(If(Only({<aR = {'aR'}>}YearMonth) = Max(TOTAL {<aR = {'aR'}>} YearMonth),

RangeAvg(Below(RangeSum(

Above(RangeSum(Above(Sum({<year, month, aR = {'aR'}>}[Amount]*-1), 0, RowNo()))),

-Sum(TOTAL {<aR = {'aR'}>}

Aggr(Sum({<year, month,aR = {'aR'}>}[Amount]*-1), YearMonth))), 0,

Max(TOTAL {<year = {$(=Max({<aR = {'aR'}>} year))}, aR = {'aR'}>} month)))), (YearMonth, (NUMERIC, desc)))

Re: Where to insert aR = {'aR'} into Average aR expression ?

paulyeo11 — Wed, 03 Aug 2016 02:59:18 GMT

Hi Sunny

Work fine now , total 3 mistake i make.

Paul