1 Reply Latest reply on Dec 7, 2018 11:34 PM by Norbert Maijoor

    Avg 3 Month Daily Sales

    Andres Galban

      Need to create a calculation that gives me the average daily sales for the last 3 months (not dynamic to each month but always fixed to the most current rolling 3 months). I have a column with the work days for each month.

       

      This is what 'ive got so far, though I feel i may have gone down a rabbit hole

       

       

         

      MonthSalesWork Days
      1/1/20182566522
      1/1/20182391622
      1/1/20181975822
      1/1/20182597122
      2/1/20182578321
      2/1/20182355721
      2/1/20182524621
      2/1/20182443921
      3/1/20182579519
      3/1/20181966319
      3/1/20182368719
      3/1/20182006719
      4/1/20182243021
      4/1/20182036021
      4/1/20182275721
      4/1/20182292721
      5/1/20182582019
      5/1/20182204119
      5/1/20182125619
      5/1/20182531719
      6/1/20182363723
      6/1/20182406323
      6/1/20182279523
      6/1/20182381523
      7/1/20182048919
      7/1/20182536419
      7/1/20181977819
      7/1/20182582019
      8/1/20182254123
      8/1/20182587423
      8/1/20182192023
      8/1/20181913923
      9/1/20182154819
      9/1/20182025719
      9/1/20182550319
      9/1/20182212219
      10/1/20182030323
      10/1/20181930523
      10/1/20182246523
      10/1/20182249123
      11/1/20182397022
      11/1/20181990922
      11/1/20182160922
      11/1/20182138622
      12/1/20182578122
      12/1/20182425922
      12/1/20182110322
      12/1/20182486222

       

       

      //sum last 3 month sales

       

       

      SUM(

      if [REPORT_DATE]  <= {fixed : max([REPORT_DATE]) }

       

      and  [REPORT_DATE]  >=

       

      date(

      dateadd('month',-2,

          {fixed : max([REPORT_DATE]) }

             )

          )

       

      then [Quantity] else 0 end

       

      )

       

       

      //this part seems to work

       

      // now divide by last 3 month work days

       

      /

       

      SUM(

      if [REPORT_DATE]  <= {fixed : max([REPORT_DATE]) }

      and    

      [REPORT_DATE] >=

      date(

      dateadd('month',-2,

          {fixed : max([REPORT_DATE]) }

             )

          )

      then

       

      //here i need to get distinct count of work days per month

       

      { FIXED [REPORT_DATE] : MIN([Work_Days])}

       

      else 0 end

      )

       

       

       

      In this scenario, what I would expect from the calc is the following :

      Dec + Nov + October Sales = 267443

      /

      Dec + Nov + October Work Days = 22 + 22 + 23 = 67

      = 3992

       

      Looking forward to figuring this one out!

        • 1. Re: Avg 3 Month Daily Sales
          Norbert Maijoor

          Hi Andres,

           

          Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.

           

           

          1. M1. Workdays: sum([Work Days])/count([Month])

           

          2. M2. Window Sum Sales: window_sum(sum([Sales]))

           

          3. M3. Window_sum Workdays: window_sum([M1. Workdays])

           

          4. M4. Average Last 3 months: [M2. Window Sum Sales]/[M3. Window_sum Workdays]

           

          5. D1. Display:

          DATEDIFF('month',[Month],today())<=2

          and DATEDIFF('month',[Month],today())>=0

           

          6. Drag the required objects to the indicated locations and filter D1. Display on TRUE

           

          workday.png

           

          Regards,

          Norbert