cumulative line chart problems

i have a table with 36 columns defined as 'table'. column B has the
date, column D has the 'delivery group' ( there are 7 potential
delivery groups), column AB has planned men on and AC has actual men
on. This sheet is done weekly so there will be up to 7 dates in column
B.

What I need is a line chart showing the dates for this week along the X
axis, and cumulative/running totals for each day for planned men on and
actual men on (one line for each) starting from the earliest date on
the source sheet. But this is only for the "SMEP Projects" delivery
group.

There will be multiple rows for each date for each delivery group.
i.e. june 1st could have 8 rows for delivery group "SMEP Projects" and
these need to be added giving a total planed men and total actual men
for this delivery group for each of the days. Then have a chart
showing the cumulative planned men and actual men for "SMEP Projects"
from the earliest date on the source shee to the latest date.

using a povottable/chart I managed to have page field as delivery
group, then select SMEP Projects. The I put the date as 'row field'
and planned men on and actual men on in the data field. This gives me
a table showing the totals for SMEP Projects for each day, but it
doesnt give me a running total and on the line chart I end up with date
on the x axis and planned and actual above each date, and then ONE line
on the line chart instead of a line for planned and a line for actual.

So I have no idea what to do now so that I get 2 lines on the chart,
one for planned, for for actual for each day.


---
Message posted from http://www.ExcelForum.com/

By default, in a PT XL should sum the planned numbers and the actual
numbers for a given group-date combination. What is it currently
showing?

To get a running total, right-click the 'Sum of Planned' PT item and
select the 'Field Settings...' menu item. In the resulting dialog box,
click the Options button. From the drop down box 'Show data as'
select 'Running Total in' From the 'Base field' select Date (though
that should be selected by default).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , neowok
says...
i have a table with 36 columns defined as 'table'. column B has the
date, column D has the 'delivery group' ( there are 7 potential
delivery groups), column AB has planned men on and AC has actual men
on. This sheet is done weekly so there will be up to 7 dates in column
B.

What I need is a line chart showing the dates for this week along the X
axis, and cumulative/running totals for each day for planned men on and
actual men on (one line for each) starting from the earliest date on
the source sheet. But this is only for the "SMEP Projects" delivery
group.

There will be multiple rows for each date for each delivery group.
i.e. june 1st could have 8 rows for delivery group "SMEP Projects" and
these need to be added giving a total planed men and total actual men
for this delivery group for each of the days. Then have a chart
showing the cumulative planned men and actual men for "SMEP Projects"
from the earliest date on the source shee to the latest date.

using a povottable/chart I managed to have page field as delivery
group, then select SMEP Projects. The I put the date as 'row field'
and planned men on and actual men on in the data field. This gives me
a table showing the totals for SMEP Projects for each day, but it
doesnt give me a running total and on the line chart I end up with date
on the x axis and planned and actual above each date, and then ONE line
on the line chart instead of a line for planned and a line for actual.

So I have no idea what to do now so that I get 2 lines on the chart,
one for planned, for for actual for each day.


---
Message posted from http://www.ExcelForum.com/

Tushar -

The problem is that he has the totals, but Pivot Charts never plot the
totals.

David -

You will need to make a regular chart from the pivot table data. It's a
little tricky to do, because lots of minor things will suddenly revert
the chart to a pivot chart. Select a blank cell away from the pivot
table and start the chart wizard. On the source data step of the hart
wizard, you must use the series tab to create your series one by one.
Using the data range tab will instantly turn the chart into a useless
pivot chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Tushar Mehta wrote:

By default, in a PT XL should sum the planned numbers and the actual
numbers for a given group-date combination. What is it currently
showing?

To get a running total, right-click the 'Sum of Planned' PT item and
select the 'Field Settings...' menu item. In the resulting dialog box,
click the Options button. From the drop down box 'Show data as'
select 'Running Total in' From the 'Base field' select Date (though
that should be selected by default).

More or less. Doesn't matter, as long as it works, eh?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

neowok wrote:
forgot about this thread hehe. i managed to do it in the end. what i
did was make a new table next to the pivottable which copies out the
pivottable values by looking at the relevant cells on the sheet where
the pivottable puts its values, and then a chart based on this new
table which then has the data I need. I just added the totals together
so that my little table contains the running totals for the days.

Then when clicking on this new chart sheet i just tell it to update
that pivottable, which in turn updates my little table...which then
keeps my new chart updated.

which is more or less what john said i think.


---
Message posted from http://www.ExcelForum.com/