pivot tables using dates, monthly summaries etc. 1

[lionelhill]

This is an off-shoot of another thread, which raised something I've always found difficult:

when I have a table containing a column of genuine dates (including the day of month, not just month and year), associated with other columns of data, and I make a pivot table from the whole thing, I find it difficult to get monthly summaries.

I'm really ashamed to be struggling with something so basic. Does anyone know a good tutorial or website explaining it?

Thanks!

 

[combo]

For me (excel 2003) refreshing the table twice solves the problem.

combo

 

[SkipVought]

hi,

I find it difficult to get monthly summaries.

1. IF you have REAL DATES and

2. IF each row in the source data table has a valid date value

...then in the PT

1. right-click the date field heading icon

2. select group and drill down to the Grouping Window, where you can GROUP dates.

Tip: Grouping dates by MONTH alone, summarizes each month for every year in the table. More than likely, it is intended to group by MONTH & YEAR. Grouping by DAYS and selecting 7 or 14 will result in WEEKLY or BI-WEEKLY aggregations.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

Skip, thanks enormously for this, and sorry not to have replied sooner.

Wow, I found the problem. It was related to what you said about all rows containing a valid date.

I had created a pivot table in Excel2003 initially, but then I used it in Excel2007. I selected the columns I needed, which Excel2003 specified as Worksheet!$A$1:$G$65536. This meant that the pivot table got a "(blank)" as its final row, which stopped grouping from working. If I create the pivot table from the outset in Excel2007, the data are specified as $A:$G, and there is no blank. Grouping then works.

 

[SkipVought]

If you ever pivot using a column containing invalid dates and you want to GROUP on the date column, then run this macro to CLEAN the PivotCache...

Sub CleanMyPivots()
'SkipVought/2007 Apr 30
'--------------------------------------------------
':cleans up pivot tables having OLD/UNUSED data hanging around.
'--------------------------------------------------
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
       pc.MissingItemsLimit = xlMissingItemsNone
       pc.Refresh
    Next
End Sub

I believe that xlbo contributed the essence of this approch. Thanks, Geoff!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

ah yes - an invaluable little piece of code if you're messing around with pivot tables - I'm impressed that I made it into your standard macros Skip....oh and hello from the other side of the world btw!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

lo mate! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

I guess I should say g'day mate!

I only moved here because I needed more of a headstart on answering the questions before you

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I'm headed to bed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

Thanks again, another one to keep safe to rescue me one day!