7 Replies Latest reply on Nov 13, 2013 2:55 PM by Shawn Wallwork

# Calculating aggregate number for n-number of weeks

So I have a set of weekly data that takes on a single calendar date, the Monday of that week (ie. 10/21, 10/14, 10/7, etc).

For every week I want it to total [Units Sold] for the last n number of weeks based on my parameter control. The catch is that I want it to do so even if the sheet itself does not show those weeks needed for the calculation.

For example, if my slider is set to total 6 weeks of sales, I do not want the first 6 weeks in my window to be null or calculate on a smaller number. I want it to pull from the data regardless. Each week would show the rolling 6 week sales respective to that date. How do I go about doing that? I tried using IF statements on my [Week Date] field but that's not working as I need it to.

• ###### 1. Re: Calculating aggregate number for n-number of weeks

Hi John,

Please check the following thread, where I have made a calculation to get the sales for Biweekly, Provided that week starts on Monday. You might be able to edit it for 6 weeks.

thanks

• ###### 2. Re: Calculating aggregate number for n-number of weeks

Thanks for the reply. Your formula is kind of like what I have now. The bottom row is your formula modified to show a few weeks instead. What I would like  is for it to show the total rolling n-weeks of sales for each of those weeks

• ###### 3. Re: Calculating aggregate number for n-number of weeks

Hi Indumon,

I am trying to show running last eight months anytime.  Any clue?

Thanks and regards,

murali.govindu@gmail.com

• ###### 4. Re: Calculating aggregate number for n-number of weeks

Hey Murali:

If I were you, I'd post a separate question along with a packaged workbook with some sample data along with a mockup of what you hope to accomplish, and someone will help you out.

• ###### 5. Re: Calculating aggregate number for n-number of weeks

Hi John,

It sounds like what you need is just a simple rolling calculations:

WINDOW_SUM(ZN(SUM([Units Sold])),-([Parameter]-1),0)

This will sum up the SUM(Units Sold) across the last N weeks starting from the (Parameter-1) weeks prior to the current week.  The ZN() should allow us to return results regardless if there is less than N weeks available.  This formula should actually work out for any level of aggregation whether the intervals displayed are weeks or months. Hope this helps!

• ###### 6. Re: Calculating aggregate number for n-number of weeks

Hi Wilson,

I needed it to pick up data not shown in the window. But I finally managed after playing around with some of the filters. Thanks!

• ###### 7. Re: Calculating aggregate number for n-number of weeks

Give yourself a correct answer so it moves out of the unanswered questions. Thanks,

--Shawn