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

# Accounting for empty months

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

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

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

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

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.