Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table calc item
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table calc item
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. "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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table calc item
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 he 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 Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table calc item
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. "Ian Grega" wrote: 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. "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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table calc item
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. "Debra Dalgleish" wrote: 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 he 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 Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table calc item
Sorry -Debra's instructions .
"Liny" wrote: 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. "Debra Dalgleish" wrote: 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 he 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 Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table calc item
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. "Liny" wrote: 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. "Debra Dalgleish" wrote: 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 he 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 Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table calc item
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. "Ian Grega" wrote: 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. "Liny" wrote: 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. "Debra Dalgleish" wrote: 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 he 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 Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table calc item
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. Liny wrote: 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. "Ian Grega" wrote: 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. "Liny" wrote: 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. "Debra Dalgleish" wrote: 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 he 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table calc field problem | Excel Discussion (Misc queries) | |||
Calc on Pivot Table | Excel Worksheet Functions | |||
Pivot table to calc by group | Excel Discussion (Misc queries) | |||
Pivot Table Grand Total - Calc Item | Excel Worksheet Functions | |||
Pivot Table - Calc Item Problem | Excel Worksheet Functions |