Pivot Table Problems
Posted by Ben Posaner on May 02, 2001 3:05 AM
Thanks to Mark i'm nearly sorted with my Pivot table problems, but i'm stuck on one point.
I've got a pivot table which has rows of "Date-Hour", Cols of "Group" & is paged by "week" & "band". All works fine And I can change the table by the differbt weeks(5) & the band. (i.e. week 3 & band 100-105) What my problem is that if a certain week doesn't have any data for a group it will miss it out completely. So some weeks i'll have 5 groups & others maybe 3 groups.
I build graphs of these tables and as the number of groups varies so do the number of series & therefore the colors used in the graph.
I've tried ticking the "show items with no data" box, but thia shows all the groups, even though they are not selected in the "Band" dropdown page menu.
Is it pssibel to "show items with no data" but only show those items which are chosen in the "band" dropdown box. (i.e. If the band is 100-105 I want to be able to see 100 101 102 103 104 105, and even if 103 conatins no data, but withou seeing all other non-required groups)
Confused? So am I
Ben
Posted by Mark W. on May 02, 2001 5:59 AM
Ben, "Show items with no data" will display all
values in the domain of the field in question --
without regard to the settings of the other
fields. So the short answer to your question is,
"No". Fortunately, there is a workaround. You
could add the missing records for each band to
your data list. Although you'd need to add a
record for each valid combination of values for
all fields except for the quantitative field
which you'd leave blank or set to 0.
Posted by Mark W. on May 02, 2001 3:20 PM
Ben, I should have also stated that once you've
added the missing values to your data set as
described below then you'd need to uncheck the
"Show items with no data" box or it will continue
to show the unwanted groups. , "Show items with no data" will display all
