2 Replies Latest reply: Aug 16, 2018 10:24 AM by Felipe Andrade RSS

    How to calculate average of sales

    Mahitha M

      Hi Experts,

      Can any one please help me on below requirement.

      In the below table I have calculated 2017 YTD Sales column by using below expression

      2017 YTD Sales


      =Sum({$<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000

      2017 Monthly Sales

      = Current month sales -Previous month sales I.e when we are in Feb its 10.53-4.87=5.66 its calculated like below

       

      RangeSum(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000,

      -Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000))


      Now for 2017 Monthly Sales need to calculate the Average. Please help me to calculate the average for 2017 Monthly Sales.


      Thanks in advance

        • Re: How to calculate average of sales
          Sunny Talwar

          May be this

           

          Avg({<Year = {'$(=Year(Max(Date))-1)'}, Month>} Aggr(

          RangeSum(Sum({<Year = {'$(=Year(Max(Date))-1)'}, Month>}Sales)/1000000,

          -Above(Sum({<Year = {'$(=Year(Max(Date))-1)'}, Month>}Sales)/1000000))

          , Year, Month))

            • Re: How to calculate average of sales
              Felipe Andrade

              You need an IF statement.

               

              like this :

              IF(Year(Max(Date))-1<1,12,Year(Max(Date))-1)

               

              in the final expression could be:

               

              IF(Year(Max(Date))-1<1,

              Avg({<Year = {'12'}, Month>} Aggr(

              RangeSum(Sum({<Year = {'12'}, Month>}Sales)/1000000,

              -Above(Sum({<Year = {'12'}, Month>}Sales)/1000000))

              , Year, Month))  ,

              Avg({<Year = {'$(=Year(Max(Date))-1)'}, Month>} Aggr(

              RangeSum(Sum({<Year = {'$(=Year(Max(Date))-1)'}, Month>}Sales)/1000000,

              -Above(Sum({<Year = {'$(=Year(Max(Date))-1)'}, Month>}Sales)/1000000))

              , Year, Month))

              )



              in this way you will have corrected the value when it is January.