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

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.