Grouping In Pivot Tables

[Guest]

Hi

I have a pivot table linked direct to an access query. In the pivot table I
have grouped the date field by year & month and set the start date as 1 Apr
2005 and end date as 31 Mar 2006. Some of the months have no data so I set
the field settings to 'include items with no data' however, this then shows
blank fields for Jan, Feb & Mar 2005, if I try filtering or hiding them out
it also hides Jan, Feb & Mar 2006.

How can I get it to only show from Apr 05 to Mar 06 and include fields where
there was no data between those dates?

Many Thanks

GLS

 

[Peo Sjoblom]

Uncheck "include items with no data", the blank data will still be there in
the included months


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

Thanks Peo, but that is how I originally had it when it wouldnt show blank
fields. Could it be because I link to the query using the 'External Data
Source' function rather than copying the data into Excell? I do this so I
can simply refersh the pivot each month to upate it.

Thanks

 

[Guest]

Hi there,

in similar cases, i could create a new field (in the mdb file already),
called YePe (Year & period), for 200501-200504 - 200505 - ....200604, then
you can de-select those periods that you don't need.

Any good?
Regards,
ANdras
(Hungary)

 

[Guest]

Thanks ANdras

As I'll be re-creating the tables for the new financial year I will
incorporate that into ithe query.

 

[Guest]

Hi GLS,

i assume, from access & pivot point, you could add this calculating field at
any time, with a function
like YePe: iif([month]>9;[year]&[month];[year]&"0"&[month]) then add to the
pivot

Best regards,
ANdras