| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivottable calculated item - how to suppress zero?
I have created a basic pivottable in Office 2007. It has three row labels
and a sum of the value of each of the row labels. I then created a calculated item that sums a few of the "third row label" values. After I have done so, I get this calculated item throughout the report even where there were no values to sum from the "second row label". So the report becomes huge with many many rows showing a zero value for the calculated item. I am not sure if this is clear. Ultimately, I am wondering if there is a way to suppress all rows for this calculated item that have zeros or is there a better way to prevent this from occurring. Sample BEFORE the calculated item is created: XXX 490 XXXXXXXXXXXX 490 Blue Shield Claims 26 Commercial Claims 113 Medicaid Claims 11 Medicare Claims 272 Paper Claims Processing - Level 1 68 YYY 16016 YYYYYYYYYYYYYYYYYY 16016 Commercial Claims 15839 Medicare Claims 177 Sample AFTER the calculated item is created: XXX 490 AAAAAAAAAAAAAAAA 0 Gov't 0 BBBBBBBBBBBBBBBBBB 0 Gov't 0 XXXXXXXXXXXXXXX 490 Commercial Claims 113 Paper Claims Processing - Level 1 68 Gov't 309 CCCCCCCCCCCCCCCC 0 Gov't 0 DDDDDDDDDDDDDDDD 0 Gov't 0 EEEEEEEEEEEEEEEEEE 0 Gov't 0 and so on. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivottable calculated item - how to suppress zero?
Unfortunately I cannot send you the data right now but to simplify my
question.... I can use a value filter to eliminate all rows (calculated or not) in the pivot that have zero value. But when I use the value filter for this purpose, it removes the label filter that I had been using on the same field. I need both filters to be in place on this field at the same time. Do you know if there is a way to use a value filter to eliminate rows from a field with zero value and also a label filter to remove specific items in the same field at the same time? "ShaneDevenshire" wrote: Hi, There are many options that may apply, but I'm not clear on exactly what you calculated formula does and where it appears. If you can you can send a copy to me -- Thanks, Shane Devenshire "HMallen" wrote: I have created a basic pivottable in Office 2007. It has three row labels and a sum of the value of each of the row labels. I then created a calculated item that sums a few of the "third row label" values. After I have done so, I get this calculated item throughout the report even where there were no values to sum from the "second row label". So the report becomes huge with many many rows showing a zero value for the calculated item. I am not sure if this is clear. Ultimately, I am wondering if there is a way to suppress all rows for this calculated item that have zeros or is there a better way to prevent this from occurring. Sample BEFORE the calculated item is created: XXX 490 XXXXXXXXXXXX 490 Blue Shield Claims 26 Commercial Claims 113 Medicaid Claims 11 Medicare Claims 272 Paper Claims Processing - Level 1 68 YYY 16016 YYYYYYYYYYYYYYYYYY 16016 Commercial Claims 15839 Medicare Claims 177 Sample AFTER the calculated item is created: XXX 490 AAAAAAAAAAAAAAAA 0 Gov't 0 BBBBBBBBBBBBBBBBBB 0 Gov't 0 XXXXXXXXXXXXXXX 490 Commercial Claims 113 Paper Claims Processing - Level 1 68 Gov't 309 CCCCCCCCCCCCCCCC 0 Gov't 0 DDDDDDDDDDDDDDDD 0 Gov't 0 EEEEEEEEEEEEEEEEEE 0 Gov't 0 and so on. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivottable calculated item - how to suppress zero?
Hi,
Well without seeing your pt, my first suggestion is to change to Tabular layout - Choose PivotTable Tools, Design, Report Layout, Show in Tabular Form. See if that helps. "HMallen" wrote: Unfortunately I cannot send you the data right now but to simplify my question.... I can use a value filter to eliminate all rows (calculated or not) in the pivot that have zero value. But when I use the value filter for this purpose, it removes the label filter that I had been using on the same field. I need both filters to be in place on this field at the same time. Do you know if there is a way to use a value filter to eliminate rows from a field with zero value and also a label filter to remove specific items in the same field at the same time? "ShaneDevenshire" wrote: Hi, There are many options that may apply, but I'm not clear on exactly what you calculated formula does and where it appears. If you can you can send a copy to me -- Thanks, Shane Devenshire "HMallen" wrote: I have created a basic pivottable in Office 2007. It has three row labels and a sum of the value of each of the row labels. I then created a calculated item that sums a few of the "third row label" values. After I have done so, I get this calculated item throughout the report even where there were no values to sum from the "second row label". So the report becomes huge with many many rows showing a zero value for the calculated item. I am not sure if this is clear. Ultimately, I am wondering if there is a way to suppress all rows for this calculated item that have zeros or is there a better way to prevent this from occurring. Sample BEFORE the calculated item is created: XXX 490 XXXXXXXXXXXX 490 Blue Shield Claims 26 Commercial Claims 113 Medicaid Claims 11 Medicare Claims 272 Paper Claims Processing - Level 1 68 YYY 16016 YYYYYYYYYYYYYYYYYY 16016 Commercial Claims 15839 Medicare Claims 177 Sample AFTER the calculated item is created: XXX 490 AAAAAAAAAAAAAAAA 0 Gov't 0 BBBBBBBBBBBBBBBBBB 0 Gov't 0 XXXXXXXXXXXXXXX 490 Commercial Claims 113 Paper Claims Processing - Level 1 68 Gov't 309 CCCCCCCCCCCCCCCC 0 Gov't 0 DDDDDDDDDDDDDDDD 0 Gov't 0 EEEEEEEEEEEEEEEEEE 0 Gov't 0 and so on. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivottable calculated item - how to suppress zero?
Hi Shane,
I tried it but it didn't work. I will email you a sample spreadsheet. Thank you for trying to assist me. "Shane Devenshire" wrote: Hi, Well without seeing your pt, my first suggestion is to change to Tabular layout - Choose PivotTable Tools, Design, Report Layout, Show in Tabular Form. See if that helps. "HMallen" wrote: Unfortunately I cannot send you the data right now but to simplify my question.... I can use a value filter to eliminate all rows (calculated or not) in the pivot that have zero value. But when I use the value filter for this purpose, it removes the label filter that I had been using on the same field. I need both filters to be in place on this field at the same time. Do you know if there is a way to use a value filter to eliminate rows from a field with zero value and also a label filter to remove specific items in the same field at the same time? "ShaneDevenshire" wrote: Hi, There are many options that may apply, but I'm not clear on exactly what you calculated formula does and where it appears. If you can you can send a copy to me -- Thanks, Shane Devenshire "HMallen" wrote: I have created a basic pivottable in Office 2007. It has three row labels and a sum of the value of each of the row labels. I then created a calculated item that sums a few of the "third row label" values. After I have done so, I get this calculated item throughout the report even where there were no values to sum from the "second row label". So the report becomes huge with many many rows showing a zero value for the calculated item. I am not sure if this is clear. Ultimately, I am wondering if there is a way to suppress all rows for this calculated item that have zeros or is there a better way to prevent this from occurring. Sample BEFORE the calculated item is created: XXX 490 XXXXXXXXXXXX 490 Blue Shield Claims 26 Commercial Claims 113 Medicaid Claims 11 Medicare Claims 272 Paper Claims Processing - Level 1 68 YYY 16016 YYYYYYYYYYYYYYYYYY 16016 Commercial Claims 15839 Medicare Claims 177 Sample AFTER the calculated item is created: XXX 490 AAAAAAAAAAAAAAAA 0 Gov't 0 BBBBBBBBBBBBBBBBBB 0 Gov't 0 XXXXXXXXXXXXXXX 490 Commercial Claims 113 Paper Claims Processing - Level 1 68 Gov't 309 CCCCCCCCCCCCCCCC 0 Gov't 0 DDDDDDDDDDDDDDDD 0 Gov't 0 EEEEEEEEEEEEEEEEEE 0 Gov't 0 and so on. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want to create a calculated item based on a calculated field | Excel Discussion (Misc queries) | |||
| Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
| pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
| PivotTable Calculated Item error | Excel Discussion (Misc queries) | |||
| PivotTable toolbar, Formulas, Calculated Item | Excel Discussion (Misc queries) | |||
Linear Mode
