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>

 

[alexjmeyer]

If any one has interest here is the solution I ended up figuring out- (Assume Date/Time is in column A)

I used the formulas =TEXT(A2,"HH") and =Text(A2,"DD") to place the value of the hour (in 24hr military time) and date into separate columns

To create an identifiable single reference to the date and hour combination I used the below formula (Assume Hour and Date are in Columns I and J respectively)-
=Text(J2,"00")&Text(I2,"00") This returns a 4 digit string combining the date and hour (i.e. 2600= 11/26 @ 12:00AM)

I then made this date/hour reference the columns in the pivot, with item as the row header and order qty as the value. With this set up the running total based on the date/hour reference worked as intended.