Help with a running total on pivot chart

[Takstein]

Hi everybody!

I have made a database where i plan the company's resources in form of hours. The idea is that every employee will put in their own data for the upcoming 4 weeks. I do this in a table where i store employeeID, weeknumber, year, PlanneHoursUsed, ActualHoursUsed.

This again i take into a pivot chart to see a forecast for the next month.

I have somehow achieved to get up a graph where i for every month can see the sum of all planned hours and all used hours (See attachment). BUT; my boss wants to see a running total instead of a "weekly image". On the graph attached one can see the numbers for every week. But i would want to see instead a running total.. For instance week1 then week2 would be the sum of 1 and 2, week 3 would be 1 ad 2 and 3 and so on. So the graph would be inclining throughout the year.

Is this possible with functions with my current data? Or do i need to have another field in my table where i store a running total? (This would be tricky when we get a lot of data and when somebody changes an old value...)

Do you understand my problem? Really hope somebody has some input on this!

 

[Takstein]

Anybody have any input on this? Is my question understandable? Any help would be appreciated!

 

[Takstein]

Getting closer!

I have managed to make something in the query now that actually works - somehow. the problem is that the query returns many similar entries of the sum. How can i display only one per week? The problem is that when i use my DSum coloumn in a chart it can only show the Count, which is useless for me..I think this is because it return details for every week..

See attached images for description.