Grouping In Pivot Tables

G

GLS

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
 
G

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
 
A

ANdras

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)
 
G

GLS

Thanks ANdras

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

ANdras

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top