Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
Sunny give me the below expression work fine , it return 5,234,000 at Table 1 is correct :-
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 :-
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
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)))
my qvf
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)))
Hi Sunny
Work fine now , total 3 mistake i make.
Paul