cumulative line chart problems

[neowok]

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

What I need is a line chart showing the dates for this week along the
axis, and cumulative/running totals for each day for planned men on an
actual men on (one line for each) starting from the earliest date o
the source sheet. But this is only for the "SMEP Projects" deliver
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" an
these need to be added giving a total planed men and total actual me
for this delivery group for each of the days. Then have a char
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 deliver
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 m
a table showing the totals for SMEP Projects for each day, but i
doesnt give me a running total and on the line chart I end up with dat
on the x axis and planned and actual above each date, and then ONE lin
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

 

[Tushar Mehta]

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

 

[Jon Peltier]

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/
_______

 

[neowok]

forgot about this thread hehe. i managed to do it in the end. what
did was make a new table next to the pivottable which copies out th
pivottable values by looking at the relevant cells on the sheet wher
the pivottable puts its values, and then a chart based on this ne
table which then has the data I need. I just added the totals togethe
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 updat
that pivottable, which in turn updates my little table...which the
keeps my new chart updated.

which is more or less what john said i think. :

 

[Jon Peltier]

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/
_______