3 Replies Latest reply: Aug 2, 2016 10:59 PM by PAUL YEO RSS

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

    PAUL YEO

      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