7 Replies Latest reply: Aug 4, 2016 8:43 PM by PAUL YEO RSS

    SET expression give YTD value , How to make it Average amount ?

    PAUL YEO

      Hi All

       

       

      Below expression from Ruben , work fine  , which give the current select date aR amount :-

       

       

      Sum({$<year, month, date={'<=$(=AddMonths(Max(date), -0))'}>}TOTAL Aggr(Sum({<year, month, date={'<=$(=AddMonths(Max(date), -0))'},aR = {'aR'}>}[Amount]), YearMonth))

       

       

      I need to modify the above expression to get the average value of aR , to make it display -5,234,734

       

       

      From Table 1

       

       

      YearMonth "Jun-2016

      (S$K)"

      June -4,619,754

      May -5,286,254

      Apr -5,702,825

      Mar -5,627,692

      Feb -5,256,865

      Jan -4,915,070

      Total -31,408,460

      no of mth 6

      Average -5,234,743

       

       

      i Try add AVG at the expression , it does not work :-

       

       

      AVG(

      Sum({$<year, month, date={'<=$(=AddMonths(Max(date), -0))'}>}TOTAL Aggr(Sum({<year, month, date={'<=$(=AddMonths(Max(date), -0))'},aR = {'aR'}>}[Amount]), YearMonth))

      )

       

       

      Hope some one can advise me.

       

       

      Paul