Thread: Pivot Table

Hi,

I am having some trouble with using Pivot Table. I have attached the excel sheet below.

In my case, i would be taking data from SQL tables and exporting to Excel "RawData" sheet. From this sheet i have designed a Pivot Table.

Code:

Location
BranchID
Total Count
ScanDateTime
DataDateTime
Longest Elapsed Time (DataDateTime - ScanDateTime)
Average Elapsed Time (Average of all Longest Elapsed Time)

Now, my requirement for designing Pivot Table is:
Location,BranchID - shown at Row Area

Total Count,ScanDateTime,DataDateTime,Longest Elapsed Time ,Average Elapsed Time - shown at Data Area

However im facing the following issues after designing the Pivot:

1) The Grand Total Average Time shown after all rows is Incorrect
2) The Grand Total Longest Time shown after all rows is Incorrect
3) I do not want to include ScanDateTime,DataDateTime in every Location Level Total row and Grand Total row but by default it is showing.
4) The format for all the Data Area fields listed above is not proper.

Have a look at the attachment and let me know your views on the above issues im facing.

There are problems with your RawData sheet. The Longest Elapsed Time is formatted as actual time (hh:mm:ss) but it should be formatted as elapsed time ([h]:mm:ss). You can see the problem in your sheet on times which are longer than 24 hours eg line 25 where ScanDateTime is 22 Oct and DataDateTime is 24 Oct but your formatting shows this as just 20 hours instead of 44 hours.

The pivot table is showing the correct values in elapsed time format.

I'm not sure how you are getting the Average Elapsed Time but it is frequently greater than the Longest Elapsed Time which can't be possible.

Thanks Huron - Ive corrected my sheet.

Now, i do not want to Show "ScanDateTime and DataDateTime" in my Summary Rows after each Location and Grand Total - How should i proceed here ??

In the Grand Total row at the end i want the "Longest Elapsed Time" to be the Longest time from the individual "Summary rows" calculated for each location. How should i proceed here ??

I have attached the updated Sheet.


Sanchit.

I would appreciate a quick response from anyone on the issue im facing.

Sanchit.

I don't think you can have summary rows for some data fields but not others - as far as I know it's all or none.

The Grand Total at the end is showing the Longest Elapsed Time ie 118:30:08 which was the value for Australia.