Pivot table / time dimension problem

[Chuck Elsham]

APOLOGIES FOR CROSSPOSTING -Posted to .Programming by mistake.
================================================================

Hello,

I'm running into a problem encountered before, a previous poster
summed it up nicely in 1996!

===========================================

I have a list of dates and an associated number for each date. (e.g.

05/11/95 10
10/11/95 11
11/11/95 14

etc., etc.)

I can construct a pivot table to summarise the data into months, years
etc. but I can't expand the data to include the dates that are not
listed
(e.g. 6/11/95). Does anyone know how to construct a pivot table that
will
list ALL dates even though some dates are missing from the table?
Failing
this, can anyone suggest the most efficient way to expand the data,
listing every date, so I can then display this data in a pivot table?

Thanks in advance


John

http://groups.google.com/groups?hl=...oups?hl=en&lr=&ie=UTF-8&q=pivot+table+missing

=============================================

John Walkenbach suggested filling the source data with the missing
values, but these would need to be replicated for every other 'slicer'
to be shown in the table (ie if we also selected by currency then
6/11/95 would have to be added for Dollars, Pounds, Yen, Euro, as
would every other date. Add another couple of slicers and we hit a
big problem)

I'm familiar with OLAP and there you would create a time dimension
filled with all dates and join to the date in the fact table.

Have things moved on in this respect in Excel 2003 - It doesn't seem
so to me?

Many thanks in advance
Rob

 

[Dave Peterson]

I'd just add a range of dates to my raw data.

And have the column(s) that I'm summing be 0.

You could put a date at the bottom of the table (say A999), then put =a999+1 in
the next cell and drag down until you're done.