Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Pivot Tables in same worksheet
I am trying to set up multiple pivot tables on the same data -- one worksheet
to routinely genrate updted reports or charts as data is added. Some charts show trend data by year, which I get by grouping the date entries to "year." Another report reflects seasonality, which I get by grouping by month. However, whenever I change the grouping on one table or chart, it changes on all others. Any ideas? So far, it seem like all other items remain particular to the table cjosen, e.g. variables, value field settings, etc. but if there is anything else that would behave like grouping, I nee to know how to keep that fixed as well. Thanks, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Pivot Tables in same worksheet
If pivot tables are based on the same pivot cache, their grouping will
be the same. Instead of basing the second pivot table on the first one, create the second pivot table based on the source data. That should result in a different pivot cache, and the grouping can be independent. AlphaBetaP wrote: I am trying to set up multiple pivot tables on the same data -- one worksheet to routinely genrate updted reports or charts as data is added. Some charts show trend data by year, which I get by grouping the date entries to "year." Another report reflects seasonality, which I get by grouping by month. However, whenever I change the grouping on one table or chart, it changes on all others. Any ideas? So far, it seem like all other items remain particular to the table cjosen, e.g. variables, value field settings, etc. but if there is anything else that would behave like grouping, I nee to know how to keep that fixed as well. Thanks, Bob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Pivot Tables in same worksheet
Debra,
Sounds great, but I can't figure out how to use a different pivot cache. My data (about 70000 lines) is in one of my work sheets in the same Excel (2007) file. Is there a different or better way to manage this? For now, I solved the problem with a work-around, by adding a second date field = to the first that I can group differently. "Debra Dalgleish" wrote: If pivot tables are based on the same pivot cache, their grouping will be the same. Instead of basing the second pivot table on the first one, create the second pivot table based on the source data. That should result in a different pivot cache, and the grouping can be independent. AlphaBetaP wrote: I am trying to set up multiple pivot tables on the same data -- one worksheet to routinely genrate updted reports or charts as data is added. Some charts show trend data by year, which I get by grouping the date entries to "year." Another report reflects seasonality, which I get by grouping by month. However, whenever I change the grouping on one table or chart, it changes on all others. Any ideas? So far, it seem like all other items remain particular to the table cjosen, e.g. variables, value field settings, etc. but if there is anything else that would behave like grouping, I nee to know how to keep that fixed as well. Thanks, Bob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Pivot Tables in same worksheet
Your workaround solves the problem, or you can add fields to calculate
the year and month, and use those in the pivot table, instead of grouping. In Excel 2007 you can create a named range that's based on the Excel table, and include a couple of blank rows at the end. Base the second pivot table on this new named range, and it will have a different pivot cache. AlphaBetaP wrote: Debra, Sounds great, but I can't figure out how to use a different pivot cache. My data (about 70000 lines) is in one of my work sheets in the same Excel (2007) file. Is there a different or better way to manage this? For now, I solved the problem with a work-around, by adding a second date field = to the first that I can group differently. "Debra Dalgleish" wrote: If pivot tables are based on the same pivot cache, their grouping will be the same. Instead of basing the second pivot table on the first one, create the second pivot table based on the source data. That should result in a different pivot cache, and the grouping can be independent. AlphaBetaP wrote: I am trying to set up multiple pivot tables on the same data -- one worksheet to routinely genrate updted reports or charts as data is added. Some charts show trend data by year, which I get by grouping the date entries to "year." Another report reflects seasonality, which I get by grouping by month. However, whenever I change the grouping on one table or chart, it changes on all others. Any ideas? So far, it seem like all other items remain particular to the table cjosen, e.g. variables, value field settings, etc. but if there is anything else that would behave like grouping, I nee to know how to keep that fixed as well. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
build a pivot table from multiple other pivot tables. | Excel Discussion (Misc queries) | |||
Two pivot tables on the same worksheet? I need help... | Excel Worksheet Functions | |||
how do I consolidate multiple pivot tables into one pivot table? | Excel Discussion (Misc queries) | |||
Multiple Pivot Tables? | Charts and Charting in Excel | |||
Multiple Pivot tables from One selection | Excel Discussion (Misc queries) |