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.
Is there a way around that?
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 #?
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.