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

[paulyeo11]

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

[paulyeo11]

my qvf

[sunny_talwar · Accepted solution]

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

[paulyeo11]

Hi Sunny

Work fine now , total 3 mistake i make.

Paul