4 Replies Latest reply: May 25, 2016 8:27 AM by Franco Giovannini RSS

    Calculating average sales

    Franco Giovannini

      Hello, I have a quite basic question. Imagine a sales table where for each product the sales amount is recorded monthly, something like this:



      ProductName     SalesDate       SalesAmount

      -------------   --------------  -----------

      A               01/11/2014      10

      A               01/03/2015      30

      B               01/04/2015      10   

      A               01/05/2015      10

      A               01/07/2015      30

      A               01/11/2015      10

      A               01/02/2016      20

      B               01/02/2016      40

      A               01/03/2016      10   

       

      For each product, I have also the date where sales has started:


      ProductName     StartSalesDate

      ------------    ----------------

      A               01/11/2014     

      B               01/03/2015     

       

      I would like to get a summary with a monthly average over a period, but taking into consideration StartSalesDate. For example, if 01/03/2016 is the max date limit, with no period selected (all-time sales) I would have


      ProductName     Total Sales     AverageMonthlySales

      ------------    --------------  ------------------

      A               120              7,05    (i.e. 120/17)

      B               50               4,61    (i.e. 50/12)



      but selecting 2015 as period, I should obtain:


      ProductName     Total Sales     AverageMonthlySales

      ------------    --------------  ------------------

      A               80              6,66    (i.e. 80/12)

      B               10              1,00    (i.e. 10/10)


      and selecting 2016


      ProductName     Total Sales     AverageMonthlySales

      ------------    --------------  ------------------

      A               30              10    (i.e. 30/3)

      B               40              13,3  (i.e. 40/3)



      What would be a correct approach to do this in your experience?


      Many thanks,

      Franco

        • Re: Calculating average sales
          Sunny Talwar

          Try this expression:

           

          =Sum(SalesAmount)


          /


          (((Year(Max(SalesDate)) * 12) + Month(RangeMin(Max({1}SalesDate), YearEnd(Max(SalesDate))))) + 1 -

          If(Year > Year(StartSalesDate),

          (((Year(YearStart(Min(SalesDate)))*12)+Month(YearStart(Min(SalesDate))))),

          (((Year(StartSalesDate)*12)+Month(StartSalesDate)))))

           

          When nothing is selected:

          Capture.PNG

           

          When 2015 is selected

          Capture.PNG

           

          When 2016 is selected:

          Capture.PNG

           

          I think two places it doesn't match is when nothing is selected you have 50/12 as 4.61, where as I think it should be 4.16667 (or 4.17)

           

          and second place is when 2016 is selected B's max date seems to be 01/02/2016, so it should be 2 months, not 3, right?

           

          Attaching the application for you to play around with.

           

          Best,

          Sunny


          • Re: Calculating average sales
            Franco Giovannini

            Thank you Swuehl and Sunny T, both your solutions works perfectly, I was fighting with an expression similar to the Sunny T one, when I realized in fact that i was missing a "total", and that for this reason the result was dependent from the selection. Thank you again for your answers, I've learnt a lot from both!