Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table from dynamic refreshed pivot table
Hi community,
I want to track action items: closed, open, overdue, and postponed activities. For this I defined a tracking sheet holding all actin items - defined as dynamic range and an associated pivot table with the structure showed in http://www.michaeltarnowski.de/temp/pivot.jpg This pivot table is fed by the data of the dynamic range and refreshes automaticaly when the sheet is activated and charted. The problem with that this table shows for each week the sum of closed, open, overdue, and postponed items only. But, in real life all open issues of the last week which are not closed in the meantime remain open in the ongoing week, i.e. the open issues of the ongoing week are the sum of all open items of this week plus the sum of open issues of the foregoing week. This is valid for overdues and postponeds as well, but not for closed items. How can rearrange this table or define a new pivot table showing the behaviour intended? - Do I need a pivot table of a pivot table? Thanks for your assistance, have a nice day Cheers Michael |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table from dynamic refreshed pivot table
Michael.Tarnowski wrote:
Hi community, I want to track action items: closed, open, overdue, and postponed activities. For this I defined a tracking sheet holding all actin items - defined as dynamic range and an associated pivot table with the structure showed in http://www.michaeltarnowski.de/temp/pivot.jpg This pivot table is fed by the data of the dynamic range and refreshes automaticaly when the sheet is activated and charted. The problem with that this table shows for each week the sum of closed, open, overdue, and postponed items only. But, in real life all open issues of the last week which are not closed in the meantime remain open in the ongoing week, i.e. the open issues of the ongoing week are the sum of all open items of this week plus the sum of open issues of the foregoing week. This is valid for overdues and postponeds as well, but not for closed items. How can rearrange this table or define a new pivot table showing the behaviour intended? - Do I need a pivot table of a pivot table? Thanks for your assistance, have a nice day Cheers Michael Hi Michael, You can get running totals in the desired fields by right-clicking a value (say, next to "Opens"), Field Settings | Options | Show data as Running Total in | Base field CW. (At least it works this way in E2003). Repeat for Overdues and Postpones. This will only partially work for you though, because the running total will break on the higher level dimension Year. You could work around the break by building up a key in your source data that combines Year and CW into a single field and using that in the PT instead. Hope this gives you some ideas. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timestamp for when Pivot table was last refreshed | Excel Discussion (Misc queries) | |||
Pivot Table vanishes when refreshed | Excel Worksheet Functions | |||
Pivot Table format changes when data is refreshed | Excel Discussion (Misc queries) | |||
Pivot Table to be refreshed whenever the web query is being refres | Excel Discussion (Misc queries) | |||
Is there a way to compare a pivot table refreshed data to old one | Excel Discussion (Misc queries) |