Grouping In Pivot Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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)
 
Thanks ANdras

As I'll be re-creating the tables for the new financial year I will
incorporate that into ithe query.
 
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
 
Back
Top