4 Replies Latest reply: Apr 16, 2018 7:53 AM by Sunny Talwar RSS

    YTD Average per month using Avg/Aggr

    An Pham

      Dear All, stalwar1

       

      i want to calculate AVG from first month of Year to selected month like formula

      Avg_YTD= (avg for each month/number of month)

       

      example:

       

      MonthValues
      Jan-20182
      Jan-20184
      Feb-20185
      Feb-20184
      Mar-20183
      Apr-20183
      Apr-20182

       

      if i selected Mar-2018,it will be calculate from Jan-2018 to Mar-2018  (3 months)

      Expected result is

      Avg_YTD = (avg(2+4) + avg(5+4) + avg(3)) /3 = 3.5

       

      but i am not sure how to calculate the % change correctly. Any help would be appreciated.

      Thanks in advanced

      An Pham

        • Re: YTD Average per month using Avg/Aggr
          youssef belloum

          Hi,

           

          Try this:

           

          convert your date field on the script like this:

           

          Date(floor(Date#(Month,'MMM-YYYY')),'MM-YYYY') as Month

           

          on your chart, use this expression:

           

          =avg({<Month={"<=$(=Max(Month))>=$(=Min(Month))"}>}Values)

          • Re: YTD Average per month using Avg/Aggr
            Sunny Talwar

            May be this

             

            Avg({<SetAnalysisOnDateOrMonth>} Aggr(Sum(Values), Month))

              • Re: YTD Average per month using Avg/Aggr
                An Pham

                Thank stalwar1

                i do as your instruction but maybe i miss something

                Here my code

                avg({
                <
                [TranDate.autoCalendar.Date]={
                            ">=$(=YearStart(Max([TranDate.autoCalendar.Date])))<=$(=Max([TranDate.autoCalendar.Date]))"
                            }
                >}aggr($(vw_%PC),[TranDate.autoCalendar.YearMonth]))
                

                //vw_%PC =sum(PC)/Sum(SC)

                 

                And here my result

                4-16-2018 4-58-49 PM.png

                When Non YearMonth or All YearMonth selected, it's correct

                 

                When YearMonth selected, it's calculate base on selected values

                Example, if 2018-Mar and 2018-Feb selected, result is AVG =(AVG(2018-Mar) +AVG(2018-Feb))/2

                instead expect result is AVG =(AVG(2018-Mar) +AVG(2018-Feb) + AVG(2018-Jan))/3   (YTD)



                Any help would be appreciated.

                Thanks in advanced

                An Pham

                  • Re: YTD Average per month using Avg/Aggr
                    Sunny Talwar

                    You will have to use your set analysis here also

                     

                    Sum({<[TranDate.autoCalendar.Date] = {">=$(=YearStart(Max([TranDate.autoCalendar.Date])))<=$(=Max([TranDate.autoCalendar.Date]))"}>} PC)/Sum({<[TranDate.autoCalendar.Date] = {">=$(=YearStart(Max([TranDate.autoCalendar.Date])))<=$(=Max([TranDate.autoCalendar.Date]))"}>}SC)