Pivot table show items with no data

[dlwearl]

If I have data like this, pre-pivoted:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Explore[/TD]
[TD]Not at all[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Personal[/TD]
[TD]Somewhat[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Take[/TD]
[TD]Very[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Obtain[/TD]
[TD]Very[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Obtain[/TD]
[TD]Very[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Take[/TD]
[TD]Not at all[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Personal[/TD]
[TD]Sucessful[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Improve[/TD]
[TD]Successful[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Obtain[/TD]
[TD]Very successful[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Obtain[/TD]
[TD]Not at all[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

When make a pivot table with Q1 in rows, Count of ID in values, and Group in filters, I can set the "Field Settings" for the Q1 to show all items with no data, and display 0 for empty items. This means that a graph made from this data will always show all Q1 values, and Q1 only, even if they contain no data. For instance, filter the pivot table by Group 2, and all 5 Q1 answers display, four of which have 0.

If I have the same data like this, a proper data set:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Question[/TD]
[TD]ID[/TD]
[TD]Response[/TD]
[TD]Group
[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]1[/TD]
[TD]Explore[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]1[/TD]
[TD]Not at all[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]2[/TD]
[TD]Personal[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]2[/TD]
[TD]Somewhat[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]3[/TD]
[TD]Take[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]3[/TD]
[TD]Very[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]4[/TD]
[TD]Obtain[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]4[/TD]
[TD]Very[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]5[/TD]
[TD]Obtain[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]5[/TD]
[TD]Very[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]6[/TD]
[TD]Take[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]6[/TD]
[TD]Not at all[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]7[/TD]
[TD]Personal[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]7[/TD]
[TD]Successful[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]8[/TD]
[TD]Improve[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]8[/TD]
[TD]Successful[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]9[/TD]
[TD]Obtain[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]9[/TD]
[TD]Very[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]10[/TD]
[TD]Obtain[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]10[/TD]
[TD]Not at all[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

When make a pivot table with Response in rows, Count of ID in values, and Group and Question in filters, setting the "Field Settings" for the Response to show all items with no data does not limit the list to the Question selected in the filter. It displays all responses with no data, including those unrelated to the filtered question, which ruins the graph.

I am unaware of a workaround. Any ideas?

Thank you!

 

[Jerry Sullivan]

Hi dlwEarl,

I'm not aware of a simple setting or option to achieve your objective. Here's one workaround that is a little kludgy, but might serve your purpose.

Add a field (I'll refer to as "SumField") to your source dataset that has the number 1 in each cell that can be used to summarize your data by Sum instead of Count.

Add dummy records at the top of your dataset in which each record represents a single Question-Response-Group combination. The set of dummy records should cover all the valid Question-Response-Group combinations. For each dummy record, place an insignificantly small fraction in the SumField (e.g. 0.0001).

In your PivotTable's field settings, uncheck the option to "Show items with no data".

With this setup, I think you can create Pivot Charts that display the only the valid combinations of Question-Responses for a group, and the "Sum" values for the "not used" items will be a small fraction that can be rounded to zero through number formatting.

While the idea of adding dummy rows to the top of the dataset might be undesirable, I think something like that is necessary for any workaround if the responses from a pool of users answering these questions doesn't include every valid combination.