Running Totals in Pivot Table

[alexjmeyer]

Hi!

I have a set of data that gives a date/time, item and sale qty (a small sample below). I'm trying to use a pivot table / chart that is grouped by day and hour along with a running total to see the cumulative total of sales by item over time. In this particular case the data spans three days. Currently the running total is resetting at each date transition but for this use I want to be able to see the running total continue across the date transitions. Any help?

Date / Time	Item	Sale Qty
1/1/2015 4:33AM	1	2
1/1/2015 1:33AM	1	3
1/1/2015 2:33AM	1	4
1/2/2015 5:10AM	1	1
1/2/2015 7:10AM	1	5
1/2/2015 6:10PM	1	7
1/3/2015 2:15PM	1	1
1/3/2015 3:15PM	1	4
1/3/2015 9:15PM	1	3

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[Weazel]

it appears your date/time is text and probably won't group by day correctly in the pivot table.
you would need to probably correct that in your raw data by inserting a space between the time and am/pm elements.

could probably do this easiest with find/replace ....so replace "AM" with " AM" and the same for "PM"

once thats corrected you should be able to group by days...

with date/time and item in rows drag sale qty to values twice...right click on one of them and go to value field settings....show values as....running total in from the drop down and date/time as the base field.

 

[alexjmeyer]

Currently in the data set the order timestamp is formatted as a date and there is a space between the Time and AM/PM.

However, I still have the issue that the running totals reset at the date transitions. If I just group by order date it gives me no issues, but when I group by date and hour that's when the problem happens. Any other ideas?

 

[SpillerBD]

Are you using "Running Total" by Item(field) or by Date/Time(field)?

 

[Weazel]

it seems to work fine as long as its grouped on either the day or the time but when they are grouped together it starts the total over for each day.

 

[SpillerBD]

I am successfully grouping by day and having a running total.
Maybe some detail difference in how we're setting up the PT, but I believe the desired result is possible.

 

[SpillerBD]

okay, I see and I too want to pull my hair out....

 

[Weazel]

yeah, I tried to add a column similar to Running Total Stops at Year End but I haven't made any progress....kind of frustrating

 

[SpillerBD]

I give up....
I get a similar result if I place the Hour part in the Columns section, but that note great.
I used a helper cell to group the date and times by hour by rounding.

Still disappointed by the limited scope of the running total.

 

[alexjmeyer]

Thanks for the help... Its definitely appreciated. I also tried creating a manual reference similar to the solution for running totals across year transitions but had no luck.