craigexcel
Active Member
- Joined
- Jun 28, 2006
- Messages
- 298
- Office Version
- 2016
- Platform
- Windows
I'm reconfiguring a workbook, where multiple sheets will each include a pivot table (PivotTable1), with the following structure, all sourced from the raw data sheet ("ALL DATA"):
1. report filter (each Unit's acronym)
2. 2 Row fields (GL account, and Trading Partner acronym)
3. 1 Column field (Period), which always has "Dec" (prior year) data as 1st column in the pivot table, current period (May) as the 2nd column, and a Calculated Item (labeled "variance", and calculated as current period less Dec prior year value ("=Period[-1]-Period[-2]")
4. Values = sum of an amount field
When I update the raw data (in the "ALL DATA" sheet) with the current period's data (e.g. changing the "Period" raw data field from, for example, "May" to "Jun"), and refresh the pivot tables in the workbook, the resulting view of the Column values in each pivot table are -- Dec as 1st column, Calculated Item as 2nd column (with accompanying #REF errors), and (now) Jun as the 3rd column. Once I drag the "variance" field in the pivot table to be the right-most column, the pivot table then displays the data correctly.
However, some of the sheets have various explanatory hardcoded data immediately below the pivot table, and when the refresh is done, all of the Trading Partner acronyms are displayed regardless of whether they apply to the Unit acronym in the report filter, which, despite the default warning, then of course overwrites some of that hardcoded data which is what I need to avoid.
Is there a way to 'force' the Calculated Item ("variance") to always remain as the rightmost column of the pivot table, despite the 'new' month's data is being added to the raw data source, and hopefully therefore only ever display the related Trading Partner acronyms in the Row field of the pivot table, and NOT overwrite any hardcoded explanatory data below the pivot table?
1. report filter (each Unit's acronym)
2. 2 Row fields (GL account, and Trading Partner acronym)
3. 1 Column field (Period), which always has "Dec" (prior year) data as 1st column in the pivot table, current period (May) as the 2nd column, and a Calculated Item (labeled "variance", and calculated as current period less Dec prior year value ("=Period[-1]-Period[-2]")
4. Values = sum of an amount field
When I update the raw data (in the "ALL DATA" sheet) with the current period's data (e.g. changing the "Period" raw data field from, for example, "May" to "Jun"), and refresh the pivot tables in the workbook, the resulting view of the Column values in each pivot table are -- Dec as 1st column, Calculated Item as 2nd column (with accompanying #REF errors), and (now) Jun as the 3rd column. Once I drag the "variance" field in the pivot table to be the right-most column, the pivot table then displays the data correctly.
However, some of the sheets have various explanatory hardcoded data immediately below the pivot table, and when the refresh is done, all of the Trading Partner acronyms are displayed regardless of whether they apply to the Unit acronym in the report filter, which, despite the default warning, then of course overwrites some of that hardcoded data which is what I need to avoid.
Is there a way to 'force' the Calculated Item ("variance") to always remain as the rightmost column of the pivot table, despite the 'new' month's data is being added to the raw data source, and hopefully therefore only ever display the related Trading Partner acronyms in the Row field of the pivot table, and NOT overwrite any hardcoded explanatory data below the pivot table?
Last edited:
