Calculating rolling averages

[BlackYellow]

Hi everyone,

I have a very difficult, very complex problem.

I have a spreadsheet of sales data which looks like this:
The 3 last columns in grey are my own formula to calculate fiscal year, week ending and territory (uses VLOOKUP to repeat data from another sheet)

When I display it on a pivot table, I sum the sales data per Customer, group it by Run (Territory) and sort it by WE (Week Ending).

The main purpose of this spreadsheet is to show me the spending patterns of each customer by territory however to see a more accurate reflection of their overall spending over the period of a financial year, I'd need to see their rolling average and how it changes over time.

How would I do this in a pivot table? If not possible in a pivot table, how can I calculate this and display it on a separate sheet?


For example:

If I were to manually calculate rolling average, it would be...

Week 1 Rolling average = Week 1 Sales / Week Number
Week 2 Rolling average = Week 1 Sales + Week 2 Sales / Week Number
Week 3 Rolling average = Week 1 Sales + Week 2 Sales + Week 3 Sales / Week Number

 

[BlackYellow]

Any takers?

 

[Fazza]

hello.

Which field is the financial year?

Would it be OK to make a cross-tab report that groups by : "Cust", then "RUN", then financial year
with columns after that being the average values by week?

regards

 

[Fazza]

I can't paste that data into Excel. So won't test this or work on it further. I'm thinking it would be helpful to have a week number field. It could be done without adding a field to the table.
Those extra three columns in grey look like they aren't needed in the table either - so long as the table that feeds them in known/available that could also be handled in the creation of the cross tab.
Here is what I was thinking, I'll leave it to you to pursue if you like. Make a cross tab query such as give the source data range a defined name - say YourTable - and via ALt-D-D-N create a query. Follow the wizard and have SQL like

TRANSFORM SUM(Sales)SELECT Cust, RUN, FY
FROM (SELECT A.Cust, A.RUN, A.FY, A.WE, SUM(B.Sales) AS [Sales]
FROM  YourTable A, YourTable B
WHERE A.Cust = B.Cust AND A.Run = B.Run AND A.FY = B.FY AND A.WE >= B.WE
GROUP BY A.Cust, A.RUN, A.FY, A.WE)
GROUP BY Cust, RUN, FY
PIVOT WE

It'd be easier to have week numbers in each FY rather than dates. Again this could be in the query definition. So this isn't a full solution but is well on the way. Cheers