pivot table calc item

[Liny]

I am running an issue with the Excel pivot table calculate item. The source
of data comes from Oracle. The data is bought into to Excel and display them
in the pivot table. There are two fields: level and type. Type has two values
of fcst and budget.

This is what the pivot table looks like:
diff is the calucated item (type[fcst] - type[budget])

type
level fcst budget diff
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
INS
OTH 5 9 -4
TE

The levels with data are fine, but it also shows those levels(INS, TE) that
have no data. How do I exclude those no data levels(eg INS, TE) from showing
up in the pivot table?

 

[Ian Grega]

Liny,

You could drop the "Type Forecast" into the Report Filter and filter out
blanks. This will then not show the levels where there is no forecast data.
Not sure if your data can have a budget amount and no forecast amount, in
that case set up a helper column with a trigger if both are blank and use
that column in the PivotTable Report Filter.

 

[Debra Dalgleish]

Instead of using a calculated item, add another copy of the Amount field
to the data area, and change it to a custom calculation, Difference
From. There are examples here:

http://www.contextures.com/xlPivot10.html

I am running an issue with the Excel pivot table calculate item. The source
of data comes from Oracle. The data is bought into to Excel and display them
in the pivot table. There are two fields: level and type. Type has two values
of fcst and budget.

This is what the pivot table looks like:
diff is the calucated item (type[fcst] - type[budget])

type
level fcst budget diff
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
INS
OTH 5 9 -4
TE

The levels with data are fine, but it also shows those levels(INS, TE) that
have no data. How do I exclude those no data levels(eg INS, TE) from showing
up in the pivot table?

 

[Liny]

If I don't put in the calc item 'diff' to the pivot table, the levels with no
fcst and budget data do not show up in the pivot table, eg INS and TE. I want
the calc item 'diff' but I don't want the blank levels to show up.

Liny,

You could drop the "Type Forecast" into the Report Filter and filter out
blanks. This will then not show the levels where there is no forecast data.
Not sure if your data can have a budget amount and no forecast amount, in
that case set up a helper column with a trigger if both are blank and use
that column in the PivotTable Report Filter.

I am running an issue with the Excel pivot table calculate item. The source
of data comes from Oracle. The data is bought into to Excel and display them
in the pivot table. There are two fields: level and type. Type has two values
of fcst and budget.

This is what the pivot table looks like:
diff is the calucated item (type[fcst] - type[budget])

type
level fcst budget diff
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
INS
OTH 5 9 -4
TE

The levels with data are fine, but it also shows those levels(INS, TE) that
have no data. How do I exclude those no data levels(eg INS, TE) from showing
up in the pivot table?

 

[Liny]

I don't understand what you mean add another copy of the 'Amount' field.

Let me give you more information of the data source.

level type data period
BNF fcst 20 FEB-08
FAC fcst 50 FEB-08
FAC budget 60 FEB-08
MKT fcst 10 FEB-08
MKT budget 5 FEB-08
OTH fcst 5 FEB-08
OTH budget 9 FEB-08
INS fcst 9 MAR-08
TE budget 20 MAR-08

On the pivot table, the period name is a page field which I have it set to
'FEB-08'.
As you can see from the data, I do not have INS and TE level for 'FEB-08'
period. But when I add the 'diff' calc item, the INS and TE show up.

Instead of using a calculated item, add another copy of the Amount field
to the data area, and change it to a custom calculation, Difference
From. There are examples here:

http://www.contextures.com/xlPivot10.html

I am running an issue with the Excel pivot table calculate item. The source
of data comes from Oracle. The data is bought into to Excel and display them
in the pivot table. There are two fields: level and type. Type has two values
of fcst and budget.

This is what the pivot table looks like:
diff is the calucated item (type[fcst] - type[budget])

type
level fcst budget diff
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
INS
OTH 5 9 -4
TE

The levels with data are fine, but it also shows those levels(INS, TE) that
have no data. How do I exclude those no data levels(eg INS, TE) from showing
up in the pivot table?

 

[Ian Grega]

Drag and drop your data field into the value section of the PivotTable again.
This will give you a repeat of your data, then follow Liny's instructions and
change this repeated data to a custom calculation (Click on the heading then
Value Field Settings then Show values As - Difference From and select base
field Type and base item Budget).

This will give you a blank column which wil be the forecast type difference
from itself which you can easily hide.

I don't understand what you mean add another copy of the 'Amount' field.

Let me give you more information of the data source.

level type data period
BNF fcst 20 FEB-08
FAC fcst 50 FEB-08
FAC budget 60 FEB-08
MKT fcst 10 FEB-08
MKT budget 5 FEB-08
OTH fcst 5 FEB-08
OTH budget 9 FEB-08
INS fcst 9 MAR-08
TE budget 20 MAR-08

On the pivot table, the period name is a page field which I have it set to
'FEB-08'.
As you can see from the data, I do not have INS and TE level for 'FEB-08'
period. But when I add the 'diff' calc item, the INS and TE show up.

Instead of using a calculated item, add another copy of the Amount field
to the data area, and change it to a custom calculation, Difference
From. There are examples here:

http://www.contextures.com/xlPivot10.html

I am running an issue with the Excel pivot table calculate item. The source
of data comes from Oracle. The data is bought into to Excel and display them
in the pivot table. There are two fields: level and type. Type has two values
of fcst and budget.

This is what the pivot table looks like:
diff is the calucated item (type[fcst] - type[budget])

type
level fcst budget diff
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
INS
OTH 5 9 -4
TE

The levels with data are fine, but it also shows those levels(INS, TE) that
have no data. How do I exclude those no data levels(eg INS, TE) from showing
up in the pivot table?

 

[Ian Grega]

Sorry -Debra's instructions .

I don't understand what you mean add another copy of the 'Amount' field.

Let me give you more information of the data source.

level type data period
BNF fcst 20 FEB-08
FAC fcst 50 FEB-08
FAC budget 60 FEB-08
MKT fcst 10 FEB-08
MKT budget 5 FEB-08
OTH fcst 5 FEB-08
OTH budget 9 FEB-08
INS fcst 9 MAR-08
TE budget 20 MAR-08

On the pivot table, the period name is a page field which I have it set to
'FEB-08'.
As you can see from the data, I do not have INS and TE level for 'FEB-08'
period. But when I add the 'diff' calc item, the INS and TE show up.

Instead of using a calculated item, add another copy of the Amount field
to the data area, and change it to a custom calculation, Difference
From. There are examples here:

http://www.contextures.com/xlPivot10.html

I am running an issue with the Excel pivot table calculate item. The source
of data comes from Oracle. The data is bought into to Excel and display them
in the pivot table. There are two fields: level and type. Type has two values
of fcst and budget.

This is what the pivot table looks like:
diff is the calucated item (type[fcst] - type[budget])

type
level fcst budget diff
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
INS
OTH 5 9 -4
TE

The levels with data are fine, but it also shows those levels(INS, TE) that
have no data. How do I exclude those no data levels(eg INS, TE) from showing
up in the pivot table?

 

[Liny]

Thanks Ian and Debra. I did as you guys instructed. Now this is what my pivot
table looks like:

Data type
data2 diff
level fcst budget (fcst) budget
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
OTH 5 9 -4

How do I remove the blank budget column? Also, how do I remove or rename the
second (fcst) to something else? It is confusing to see the a second fcst
column.

Drag and drop your data field into the value section of the PivotTable again.
This will give you a repeat of your data, then follow Liny's instructions and
change this repeated data to a custom calculation (Click on the heading then
Value Field Settings then Show values As - Difference From and select base
field Type and base item Budget).

This will give you a blank column which wil be the forecast type difference
from itself which you can easily hide.

I don't understand what you mean add another copy of the 'Amount' field.

Let me give you more information of the data source.

level type data period
BNF fcst 20 FEB-08
FAC fcst 50 FEB-08
FAC budget 60 FEB-08
MKT fcst 10 FEB-08
MKT budget 5 FEB-08
OTH fcst 5 FEB-08
OTH budget 9 FEB-08
INS fcst 9 MAR-08
TE budget 20 MAR-08

On the pivot table, the period name is a page field which I have it set to
'FEB-08'.
As you can see from the data, I do not have INS and TE level for 'FEB-08'
period. But when I add the 'diff' calc item, the INS and TE show up.

Instead of using a calculated item, add another copy of the Amount field
to the data area, and change it to a custom calculation, Difference
From. There are examples here:

http://www.contextures.com/xlPivot10.html

Liny wrote:
I am running an issue with the Excel pivot table calculate item. The source
of data comes from Oracle. The data is bought into to Excel and display them
in the pivot table. There are two fields: level and type. Type has two values
of fcst and budget.

This is what the pivot table looks like:
diff is the calucated item (type[fcst] - type[budget])

type
level fcst budget diff
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
INS
OTH 5 9 -4
TE

The levels with data are fine, but it also shows those levels(INS, TE) that
have no data. How do I exclude those no data levels(eg INS, TE) from showing
up in the pivot table?

 

[Debra Dalgleish]

To change the name of the second fcst column, click on that cell, and
type the heading that you want. Just don't use a heading that's the same
as one of the fields in the pivot table.

To hide the blank budget column, you can manually hide that column in
the worksheet. There's no setting to hide it in the pivot table.

Thanks Ian and Debra. I did as you guys instructed. Now this is what my pivot
table looks like:

Data type
data2 diff
level fcst budget (fcst) budget
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
OTH 5 9 -4

How do I remove the blank budget column? Also, how do I remove or rename the
second (fcst) to something else? It is confusing to see the a second fcst
column.


:

Drag and drop your data field into the value section of the PivotTable again.
This will give you a repeat of your data, then follow Liny's instructions and
change this repeated data to a custom calculation (Click on the heading then
Value Field Settings then Show values As - Difference From and select base
field Type and base item Budget).

This will give you a blank column which wil be the forecast type difference
from itself which you can easily hide.

:

I don't understand what you mean add another copy of the 'Amount' field.

Let me give you more information of the data source.

level type data period
BNF fcst 20 FEB-08
FAC fcst 50 FEB-08
FAC budget 60 FEB-08
MKT fcst 10 FEB-08
MKT budget 5 FEB-08
OTH fcst 5 FEB-08
OTH budget 9 FEB-08
INS fcst 9 MAR-08
TE budget 20 MAR-08

On the pivot table, the period name is a page field which I have it set to
'FEB-08'.
As you can see from the data, I do not have INS and TE level for 'FEB-08'
period. But when I add the 'diff' calc item, the INS and TE show up.


:


Instead of using a calculated item, add another copy of the Amount field
to the data area, and change it to a custom calculation, Difference
From. There are examples here:

http://www.contextures.com/xlPivot10.html

Liny wrote:

I am running an issue with the Excel pivot table calculate item. The source
of data comes from Oracle. The data is bought into to Excel and display them
in the pivot table. There are two fields: level and type. Type has two values
of fcst and budget.

This is what the pivot table looks like:
diff is the calucated item (type[fcst] - type[budget])

type
level fcst budget diff
BNF 20 20
FAC 50 60 -10
MKT 10 5 5
INS
OTH 5 9 -4
TE

The levels with data are fine, but it also shows those levels(INS, TE) that
have no data. How do I exclude those no data levels(eg INS, TE) from showing
up in the pivot table?