4 Replies Latest reply on Aug 3, 2018 1:58 PM by Jon Boschee

    Accounting for empty months

    Jon Boschee

      Hi everyone,

       

      I have a 6 month rolling average that is not accounting for a month with no sales.  It will take the next month to complete the 6 months.   My data is below and is the a way to have the calculation know to insert Feb as zero?  I am using INDEX() to count the months.

       

       

      June   100

      May    150

      Mar    250

       

      Jan    125

      Dec   500

        • 1. Re: Accounting for empty months
          Jeff Strauss

          If your month is a discrete pill on the row or column shelf, then you can right click on the axis, and turn on "show missing values".  Does this work for you?

           

          • 2. Re: Accounting for empty months
            Jon Boschee

            I think that worked perfectly!  It leads to a follow up questions though.  There will be some cases where I will have both negative sales and units sold (free goods).  With my window sum(sales)/window sum(units), this will result in a positive number. 

             

            -500/-100=5

             

            Is there a way around that?

            • 3. Re: Accounting for empty months
              Jeff Strauss

              oh good, I'm glad it worked.  If it's helpful or answered, please mark as so.  In terms of the window_sum, it really depends on your span of the window, if it's across the entire 6 months and both are positive, then it's going to show a positive #, but if it's across the specific month and sales are negative, then it should show as negative.  Does this help?

               

               

              Oh, why is your units negative?  Maybe can you put an ABS() around this to make it always a positive #?

              • 4. Re: Accounting for empty months
                Jon Boschee

                 

                Here's what it looks like.  Both my sales and volume are negative which then returns a positive 6 month avg.  The negative volume is an accounting adjustment.

                 

                I don't think I can put ABS() around it because then when there are months with only negative volume combined with months with positive volume, it would give the correct sum.