Using Pivot Table to analyze multiple variables in satisfaction survey

[gotesman]

I'm trying to create a worksheet that will help us analyze the results
of a satisfaction survey.

Our data is structured in the following way:

--------------------------------------------------------
|QUARTER GENDER OCCUPATION SATISFACTION
--------------------------------------------------------
|2006Q1 MALE STUDENT 5
|2006Q1 MALE RETIRED 4
|2006Q1 FEMALE COMPUTERS 2
|2006Q2 MALE STUDENT 1
|2006Q2 FEMALE STUDENT 5
|2006Q2 FEMALE COMPUTERS 5
--------------------------------------------------------

We have a couple dozen columns, one for each question in our survey
(for simplicity, I'm using just three questions in this example).


I'm looking to create a summary (preferably in a pivot table) that will
show the percentage of respondents with a satisfaction score of either
4 or 5, broken out by the responses to either the gender or occupation
questions (using a List Box form to determine which question is
analyzed). The summary table should look something like:

-------------------------
|GENDER 2006Q1 2006Q2
-------------------------
|MALE 100% 0%
|FEMALE 0% 50%
-------------------------

or

--------------------------------
|OCCUPATION 2006Q1 2006Q2
--------------------------------
|STUDENT 100% 50%
|RETIRED 100% 0%
|COMPUTERS 0% 0%
--------------------------------


Using this analysis, we'd be able to say things like "it looks like
people in the computers industry are consistently not satisfied with
our product" or "it looks like we've improved our sanctification scores
with females".


What I already have is a List Box form with each question in our
survey. Each time the user selects a new question from the list, I
have VBA instructions to change the PivotField orientation of a simple
Pivot Table so that it gives me a breakout of each response (in the
rows) while keeping it broken out by quarter (in the columns). A Pivot
Chart is then automatically updated, illustrating how our customer base
is changing over time (we can see that more females took our survey
this quarter than last).

The VBA code for that looks like:

Sheets("sheet3").PivotTables("pt1").ColumnFields(1).Orientation =
xlHidden
Sheets("sheet3").PivotTables("pt1").PivotFields(Range("questions").Cells(Sheets("Sheet2").Shapes("row_attribute").ControlFormat.Value).Value).Orientation
= xlColumnField


and if a user selects "GENDER" from the List Box, the resulting Pivot
Table looks like:

-------------------------
|GENDER 2006Q1 2006Q2
-------------------------
|MALE 2 1
|FEMALE 1 2
-------------------------


I'd love to use pivot tables for a similarly elegant solution, but have
been unable to come up with something great in two+ days of work. I'm
looking at an alternative method using array formulas to count rows
that satisfy multiple conditions (such as quarter = "2006Q1", gender =
"female", satisfaction = "4 or 5") using a formula like:

=SUM((raw!$B$7:$B$588="2006Q1")*(raw!$W$7:$W$588="female")*((raw!$U$7:$U$588=4)+(raw!$U$7:$U$588=5)))

but it would be messy and not as elegant or simple to maintain as I'd
like.


I hope I've communicated the question clearly (hopefully the spacing
for the tables is readable) and I would be unbelievably grateful if
someone had feedback on this problem.


Thanks so much!
Alon

 

[gotesman]

Replying to my own question.

I couldn't get anyone to bite, but in the meanwhile I was able to make
some progress.

I realized that I can filter out rows with satisfaction of 1, 2, or 3
using the Page Field. So I set up my pivot table using:

Row field -- GENDER (or occupation, depending on what my List Box is
set to)
Column field -- QUARTER
Page field -- SATISFACTION
Date field -- COUNT of QUARTER


I can then filter out people with low satisfaction levels by
double-clicking on the page field and selecting the response options in
the "Hide Items" box.


That leaves me with a table like:

-------------------------
|GENDER 2006Q1 2006Q2
-------------------------
|MALE 2 0
|FEMALE 0 2
-------------------------

So I'm close. I just need to take these numbers and divide them by the
*non-filtered* totals. I've been looking around and haven't quite
found how to do that -- perhaps using a Calculated Field? (from the
Pivot Table toolbar selecting Pivot Table -> Formula -> Calculated
Field)


I'd even appreciate any tips on writing a subroutine that will neatly
divide values in two pivot tables with identical layouts (the filtered
vs. the unfiltered) and place the data in a way that can be charted.

Again -- any help here would be tremendous.