Calculated Field/Item in a Pivot
 

Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows of data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which displays the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of the last
column of the pivot, but this is no good as the references will be thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table so that it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is an Average and
this seems to be causing a problem.

Calculated Field/Item in a Pivot
 

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows of data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which displays the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of the last
column of the pivot, but this is no good as the references will be
thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table so that it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is an Average
and
this seems to be causing a problem.

Calculated Field/Item in a Pivot
 

Hi Roger and thanks for the quick reply. I'm afraid that didn't work though.

I got a "your formula contains an error" message and it reverted to the
Calculated Field wizard highlighting the word Non of Non Diabetic in the
formula. I typed it as you did. Is naming the field DIABETIC confusing things
as this also one of the two possible values found in that column?

Matt

"Roger Govier" wrote:

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows of data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which displays the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of the last
column of the pivot, but this is no good as the references will be
thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table so that it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is an Average
and
this seems to be causing a problem.

Calculated Field/Item in a Pivot
 

Hi Matt

I set myself up a very small table with your data following your first
posting with headings of OPCS, Diabetic and Non Diabetic.
I then created the PT and inserted a calculated field as described, and
all worked well.
Did you enclose the Non Diabetic in single quotes?

The formula I posted, is one that I copied (using Ctrl+C) from the pane
in the setup of Insert Field.
Try copying my formula and pasting with Ctrl+V into the setup pane. If
it worked for me, then it should for you.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi Roger and thanks for the quick reply. I'm afraid that didn't work
though.

I got a "your formula contains an error" message and it reverted to
the
Calculated Field wizard highlighting the word Non of Non Diabetic in
the
formula. I typed it as you did. Is naming the field DIABETIC confusing
things
as this also one of the two possible values found in that column?

Matt

"Roger Govier" wrote:

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows of
data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which displays
the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of the
last
column of the pivot, but this is no good as the references will be
thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table so that
it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is an
Average
and
this seems to be causing a problem.

Calculated Field/Item in a Pivot
 

Yes I tried the copy & pasting your formula including the single quotes but
get the same error.

I'm not sure the table you built is the same as mine. The source data would
look like this

Code LoS DIABETIC
X358 1.2 Diabetic
X357 2.2 Diabetic
X358 1.8 Non Diabetic
A559 3.4 Diabetic
X367 0.3 Non Diabetic
X358 0.9 Non Diabetic
A559 3.4 Diabetic

etc

So one column to flag Diabetics, as opposed to seperate Diabetic and Non
Diabetic columns. These two values (Diabetic/ Non Diabetic) or what gives me
my two columns in the Pivot.

Is that how yours was set out?

If so, I don't know what else I could have done wrong.


"Roger Govier" wrote:

Hi Matt

I set myself up a very small table with your data following your first
posting with headings of OPCS, Diabetic and Non Diabetic.
I then created the PT and inserted a calculated field as described, and
all worked well.
Did you enclose the Non Diabetic in single quotes?

The formula I posted, is one that I copied (using Ctrl+C) from the pane
in the setup of Insert Field.
Try copying my formula and pasting with Ctrl+V into the setup pane. If
it worked for me, then it should for you.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi Roger and thanks for the quick reply. I'm afraid that didn't work
though.

I got a "your formula contains an error" message and it reverted to
the
Calculated Field wizard highlighting the word Non of Non Diabetic in
the
formula. I typed it as you did. Is naming the field DIABETIC confusing
things
as this also one of the two possible values found in that column?

Matt

"Roger Govier" wrote:

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows of
data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which displays
the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of the
last
column of the pivot, but this is no good as the references will be
thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table so that
it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is an
Average
and
this seems to be causing a problem.

Calculated Field/Item in a Pivot
 

Hi Matt
No my data was entirely wrong for you.
Using your small subset, I put Code as a Row field, Diabetic as a Column
field and Average of LoS as a Data field.
I then inserted Average of LoS a second time, clicked Advanced, and Show
data as Difference from.
I chose Diabetic as my Base Field and Diabetic as my Base item.

I think this gives the result almost as you want.
Drag the Data button to the Total area to see the columns side by side
instead of beneath each other.
I also went into Table Options and de-selected Grand Total by Row.
You end up with 5 columns, the third, being Average of LoS2 for Diabetic
(which is always empty as it is the difference from itself).
I just hid column C to not show it and have a clearer result.

I hope this helps.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Yes I tried the copy & pasting your formula including the single
quotes but
get the same error.

I'm not sure the table you built is the same as mine. The source data
would
look like this

Code LoS DIABETIC
X358 1.2 Diabetic
X357 2.2 Diabetic
X358 1.8 Non Diabetic
A559 3.4 Diabetic
X367 0.3 Non Diabetic
X358 0.9 Non Diabetic
A559 3.4 Diabetic

etc

So one column to flag Diabetics, as opposed to seperate Diabetic and
Non
Diabetic columns. These two values (Diabetic/ Non Diabetic) or what
gives me
my two columns in the Pivot.

Is that how yours was set out?

If so, I don't know what else I could have done wrong.


"Roger Govier" wrote:

Hi Matt

I set myself up a very small table with your data following your
first
posting with headings of OPCS, Diabetic and Non Diabetic.
I then created the PT and inserted a calculated field as described,
and
all worked well.
Did you enclose the Non Diabetic in single quotes?

The formula I posted, is one that I copied (using Ctrl+C) from the
pane
in the setup of Insert Field.
Try copying my formula and pasting with Ctrl+V into the setup pane.
If
it worked for me, then it should for you.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi Roger and thanks for the quick reply. I'm afraid that didn't
work
though.

I got a "your formula contains an error" message and it reverted to
the
Calculated Field wizard highlighting the word Non of Non Diabetic
in
the
formula. I typed it as you did. Is naming the field DIABETIC
confusing
things
as this also one of the two possible values found in that column?

Matt

"Roger Govier" wrote:

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows of
data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which displays
the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of
the
last
column of the pivot, but this is no good as the references will
be
thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table so
that
it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is an
Average
and
this seems to be causing a problem.

Calculated Field/Item in a Pivot
 

OK, definitely see where you're coming from with this approach and I'm nearly
there.

I created the "% Difference From" field, but I don't follow what you mean by
"drag the Data Button to the Total Area". What is the "Total area"?

I get % values I want, but they appear under the first value:

Code Data Diabetic Non Diabetic
V481 Average of LoS 1.8 4.7
Average of LoS2 -61.23%

If there's a way of getting that % figure to appear on the same row, so as
to get 1 row per code, I don't know it, but it would be useful, I've needed
this before!

Thanks for your patience

Matt

"Roger Govier" wrote:

Hi Matt
No my data was entirely wrong for you.
Using your small subset, I put Code as a Row field, Diabetic as a Column
field and Average of LoS as a Data field.
I then inserted Average of LoS a second time, clicked Advanced, and Show
data as Difference from.
I chose Diabetic as my Base Field and Diabetic as my Base item.

I think this gives the result almost as you want.
Drag the Data button to the Total area to see the columns side by side
instead of beneath each other.
I also went into Table Options and de-selected Grand Total by Row.
You end up with 5 columns, the third, being Average of LoS2 for Diabetic
(which is always empty as it is the difference from itself).
I just hid column C to not show it and have a clearer result.

I hope this helps.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Yes I tried the copy & pasting your formula including the single
quotes but
get the same error.

I'm not sure the table you built is the same as mine. The source data
would
look like this

Code LoS DIABETIC
X358 1.2 Diabetic
X357 2.2 Diabetic
X358 1.8 Non Diabetic
A559 3.4 Diabetic
X367 0.3 Non Diabetic
X358 0.9 Non Diabetic
A559 3.4 Diabetic

etc

So one column to flag Diabetics, as opposed to seperate Diabetic and
Non
Diabetic columns. These two values (Diabetic/ Non Diabetic) or what
gives me
my two columns in the Pivot.

Is that how yours was set out?

If so, I don't know what else I could have done wrong.


"Roger Govier" wrote:

Hi Matt

I set myself up a very small table with your data following your
first
posting with headings of OPCS, Diabetic and Non Diabetic.
I then created the PT and inserted a calculated field as described,
and
all worked well.
Did you enclose the Non Diabetic in single quotes?

The formula I posted, is one that I copied (using Ctrl+C) from the
pane
in the setup of Insert Field.
Try copying my formula and pasting with Ctrl+V into the setup pane.
If
it worked for me, then it should for you.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi Roger and thanks for the quick reply. I'm afraid that didn't
work
though.

I got a "your formula contains an error" message and it reverted to
the
Calculated Field wizard highlighting the word Non of Non Diabetic
in
the
formula. I typed it as you did. Is naming the field DIABETIC
confusing
things
as this also one of the two possible values found in that column?

Matt

"Roger Govier" wrote:

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows of
data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which displays
the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of
the
last
column of the pivot, but this is no good as the references will
be
thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table so
that
it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is an
Average
and
this seems to be causing a problem.

Calculated Field/Item in a Pivot
 

Hi Matt

I shouldn't have used the word "area".
Drag the Data Button to where the Total appears in the PT, and the data
will appear side by side.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
OK, definitely see where you're coming from with this approach and I'm
nearly
there.

I created the "% Difference From" field, but I don't follow what you
mean by
"drag the Data Button to the Total Area". What is the "Total area"?

I get % values I want, but they appear under the first value:

Code Data Diabetic Non Diabetic
V481 Average of LoS 1.8 4.7
Average of LoS2 -61.23%

If there's a way of getting that % figure to appear on the same row,
so as
to get 1 row per code, I don't know it, but it would be useful, I've
needed
this before!

Thanks for your patience

Matt

"Roger Govier" wrote:

Hi Matt
No my data was entirely wrong for you.
Using your small subset, I put Code as a Row field, Diabetic as a
Column
field and Average of LoS as a Data field.
I then inserted Average of LoS a second time, clicked Advanced, and
Show
data as Difference from.
I chose Diabetic as my Base Field and Diabetic as my Base item.

I think this gives the result almost as you want.
Drag the Data button to the Total area to see the columns side by
side
instead of beneath each other.
I also went into Table Options and de-selected Grand Total by Row.
You end up with 5 columns, the third, being Average of LoS2 for
Diabetic
(which is always empty as it is the difference from itself).
I just hid column C to not show it and have a clearer result.

I hope this helps.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Yes I tried the copy & pasting your formula including the single
quotes but
get the same error.

I'm not sure the table you built is the same as mine. The source
data
would
look like this

Code LoS DIABETIC
X358 1.2 Diabetic
X357 2.2 Diabetic
X358 1.8 Non Diabetic
A559 3.4 Diabetic
X367 0.3 Non Diabetic
X358 0.9 Non Diabetic
A559 3.4 Diabetic

etc

So one column to flag Diabetics, as opposed to seperate Diabetic
and
Non
Diabetic columns. These two values (Diabetic/ Non Diabetic) or what
gives me
my two columns in the Pivot.

Is that how yours was set out?

If so, I don't know what else I could have done wrong.


"Roger Govier" wrote:

Hi Matt

I set myself up a very small table with your data following your
first
posting with headings of OPCS, Diabetic and Non Diabetic.
I then created the PT and inserted a calculated field as
described,
and
all worked well.
Did you enclose the Non Diabetic in single quotes?

The formula I posted, is one that I copied (using Ctrl+C) from the
pane
in the setup of Insert Field.
Try copying my formula and pasting with Ctrl+V into the setup
pane.
If
it worked for me, then it should for you.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi Roger and thanks for the quick reply. I'm afraid that didn't
work
though.

I got a "your formula contains an error" message and it reverted
to
the
Calculated Field wizard highlighting the word Non of Non
Diabetic
in
the
formula. I typed it as you did. Is naming the field DIABETIC
confusing
things
as this also one of the two possible values found in that
column?

Matt

"Roger Govier" wrote:

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

--
Regards

Roger Govier


"Matt D Francis" wrote
in
message
...
Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows
of
data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which
displays
the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of
the
last
column of the pivot, but this is no good as the references
will
be
thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table
so
that
it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is
an
Average
and
this seems to be causing a problem.

Calculated Field/Item in a Pivot
 

OK I've dragged it all over the place andl am getting pretty much what I want
now, thanks a lot for your help.

Matt

"Roger Govier" wrote:

Hi Matt

I shouldn't have used the word "area".
Drag the Data Button to where the Total appears in the PT, and the data
will appear side by side.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
OK, definitely see where you're coming from with this approach and I'm
nearly
there.

I created the "% Difference From" field, but I don't follow what you
mean by
"drag the Data Button to the Total Area". What is the "Total area"?

I get % values I want, but they appear under the first value:

Code Data Diabetic Non Diabetic
V481 Average of LoS 1.8 4.7
Average of LoS2 -61.23%

If there's a way of getting that % figure to appear on the same row,
so as
to get 1 row per code, I don't know it, but it would be useful, I've
needed
this before!

Thanks for your patience

Matt

"Roger Govier" wrote:

Hi Matt
No my data was entirely wrong for you.
Using your small subset, I put Code as a Row field, Diabetic as a
Column
field and Average of LoS as a Data field.
I then inserted Average of LoS a second time, clicked Advanced, and
Show
data as Difference from.
I chose Diabetic as my Base Field and Diabetic as my Base item.

I think this gives the result almost as you want.
Drag the Data button to the Total area to see the columns side by
side
instead of beneath each other.
I also went into Table Options and de-selected Grand Total by Row.
You end up with 5 columns, the third, being Average of LoS2 for
Diabetic
(which is always empty as it is the difference from itself).
I just hid column C to not show it and have a clearer result.

I hope this helps.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Yes I tried the copy & pasting your formula including the single
quotes but
get the same error.

I'm not sure the table you built is the same as mine. The source
data
would
look like this

Code LoS DIABETIC
X358 1.2 Diabetic
X357 2.2 Diabetic
X358 1.8 Non Diabetic
A559 3.4 Diabetic
X367 0.3 Non Diabetic
X358 0.9 Non Diabetic
A559 3.4 Diabetic

etc

So one column to flag Diabetics, as opposed to seperate Diabetic
and
Non
Diabetic columns. These two values (Diabetic/ Non Diabetic) or what
gives me
my two columns in the Pivot.

Is that how yours was set out?

If so, I don't know what else I could have done wrong.


"Roger Govier" wrote:

Hi Matt

I set myself up a very small table with your data following your
first
posting with headings of OPCS, Diabetic and Non Diabetic.
I then created the PT and inserted a calculated field as
described,
and
all worked well.
Did you enclose the Non Diabetic in single quotes?

The formula I posted, is one that I copied (using Ctrl+C) from the
pane
in the setup of Insert Field.
Try copying my formula and pasting with Ctrl+V into the setup
pane.
If
it worked for me, then it should for you.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi Roger and thanks for the quick reply. I'm afraid that didn't
work
though.

I got a "your formula contains an error" message and it reverted
to
the
Calculated Field wizard highlighting the word Non of Non
Diabetic
in
the
formula. I typed it as you did. Is naming the field DIABETIC
confusing
things
as this also one of the two possible values found in that
column?

Matt

"Roger Govier" wrote:

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

--
Regards

Roger Govier


"Matt D Francis" wrote
in
message
...
Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows
of
data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which
displays
the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of
the
last
column of the pivot, but this is no good as the references
will
be
thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table
so
that
it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is
an
Average
and
this seems to be causing a problem.