Pivot table with date - time & number of transactions

**wv1973** · 10-08-2009, 03:32 PM

Well I thought this would be a simple pivot table to throw together, but it's got me stumped.

Basically I have 3 columns of date. Transactions, date and time of day. What I'm trying to do is create a pivot table which compares the transactions at a specific hour day by day.

I've attached the data, now for some reason when I tried I had the hours in the row field. The date in the column field and placed the transactions in the data item field. When the pivot table displayed all the date fields showed 1's instead of the actual data values.

Can someone point me where I'm going wrong, thanks..

**Cheeky Charlie** · 10-08-2009, 05:05 PM

now for some reason when I tried I had the hours in the row field

Seems to make sense... hurr durr, I'm funny.

Anyway - I rather suspect you are counting instances of transaction count - rather than summing them. What do you see in the top left of your pivot? If it's "Count of transaction count", right-click -> field settings -> summarise by "Sum"

<edit>
issue 2!
You will also needs to turn your numbers into numbers (they are stored as text) - simple method:
Put 1 in a random cell
Copy it
Select Transaction count data (not whole column)
Alt+e, s (brings up paste special dialogue)
check "multiply"
OK

Get back to us if that's not it, or if it is, for that matter.

HTH