Pivot tables

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

This is an abstract of a much larger dbase/spreadsheet.
This is the way it is layed out.

Locat Doc Impression
Rad Jones FALSE
Rad Jones TRUE
Rad Jones TRUE
Rad Monroe TRUE
CT Monroe TRUE
Rad Monroe TRUE
Rad DOE TRUE
CT Monroe TRUE
Rad Monroe TRUE
MRI DOE FALSE
MRI JONES TRUE
Rad Monroe TRUE
Rad Monroe TRUE
Rad JONES FALSE
NM JONES TRUE
Rad Monroe FALSE
Rad Monroe FALSE
Rad Monroe FALSE
MRI Monroe TRUE
Rad JONES TRUE
CT Monroe TRUE
Rad Monroe TRUE
Rad Monroe TRUE

What I'm having problems with is this. I need to report
the information similar to a pivot table:

Jones
MRI
CT
RAD
Monroe
MRI
CT
RAD
But cannot get a calculated field to count the number
of "Trues" only. I want to end up with a percentage of
compliance

Jones
MRI Number True 1
CT Number True 0
RAD Number True 3
Monroe
MRI Number True XX
CT Number True XX
RAD Number True XX

You get the idea. I don't want to change the axis of the
data since this is the way it comes out of Access.
 
Pivot Table / 'Doc' to Row fields / 'Locat' to Row fields but to right of Doc /
'Impression' to Data field / 'Impression' also to Column field but use dropdown
and deselect False.
 
To calculate the percentage of compliance, you'll need at least one more
field in the pivot table. If your data table only has three columns, add
one more -- add a heading, e.g. Count, and enter a 1 in each row of data.

Refresh the pivot table so it includes this field.

Set up the pivot table with Doc and Locat in the row area, Impression in
the column area, and Count in the data area (add it as Count of Count).

Select the field button for Impression
From the PivotTable menu, choose PivotTable>Formulas>Calculated Item
Type a name for the formula, e.g Comply
In the formula box, type:

=IF(('FALSE'+'TRUE')=0,0,'TRUE'/('FALSE'+'TRUE'))
Click Add, click OK
In the Pivot Table, select the Comply column, and format as percentage.
 
Back
Top