Pivot Table Problem

[bradleyjohnrob]

I'm trying to get a 52 week sales/inventory report to auto fill from a data dump in the form of a pivot table. The only problem is that if there is no sales data from a week then that week does not show up in my pivot table because of the lack of source data. For example if there were no sales in week three and I made a pivot table grouped by week then there would only be three lines of data. I've unsuccessfully tried using a lookup function to fill the data in.

I've looked into the getpivotdata function, but I don't know if that will help me at all.

Is there anyone that has run into this problem and found a fix?

 

[Fazza]

welcome

One way is to create the pivot table from two tables - one your data dump & the other a list of all weeks. The relationship between the tables to give the dataset might be like

SELECT D.some_fields, W.week
FROM Weeks W LEFT OUTER JOIN DataDump D ON W.week = D.week

Here is one way to manually set it up. Give the data dump a defined named "DataDump". On another worksheet defined name "Weeks" - being a table with a header and then listed below the 52 weeks you want. Save the file. From a new file, ALT-D-P and choose the external data option at the first step: follow the wizard. Excel files, OK, choose the Excel data file, OK, see the data tables, choose the fields you want, OK. If you get a message about 'the wizard can't continue...' just OK & continue. When you see the MS Query GUI either drag & join between the tables and edit the link to make it an outer join, or, via the SQL button enter the SQL. SQL will be like above but to suit your exact set up. When OK, via the 'open door' icon exit MS Query and then finish the pivot table. The worksheet containing the pivot table can be moved into the source data file if you wish.

regards