Ask Your Question
0

Pivot table - date/time not compiling and avg time (hh:mm:ss) showing empty

asked 2018-11-16 22:55:06 +0200

VirtualMechanic gravatar image

updated 2018-11-16 22:57:41 +0200

I am trying to create a fairly simply pivot table to quickly compile and break down some numbers from our ticketing system. The original file format was .CSV, which I saved as .ODS to edit and what not.

I set the format of the data fields as I would like them (eg. open date = DD-MM-YYYY HH:MM:SS).

Pivot table settings:

image description

Example data:

image description

The result:

image description

The desired result:

image description

Meaning an overview per day of the average response time.

I cannot get this to work for the live of me. I have used the same function in Excel, which worked fine (with the same source data) but somehow can't get it to work in LibreOffice Calc.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2018-11-16 23:40:15 +0200

m.a.riosv gravatar image

By the data looks date values are imported as text, so when opening/importing csv set up the column to a date type so it is imported properly.

After create the pivot table go to any cell on date column and with [F12] or Menu/Data/Group & Outline/Group you can select the unit.

image description

edit flag offensive delete link more

Comments

Thank you very much! I found an even easier way to get the date/time format right, which was ticking the box 'Detect sepcial numbers' when loading in the .CSV. This did the trick for me.

VirtualMechanic gravatar imageVirtualMechanic ( 2018-11-17 00:03:40 +0200 )edit
1

answered 2018-11-16 23:32:45 +0200

updated 2018-11-16 23:39:03 +0200

Put cursor to any datetime cell in the pivot table; DataGroup and OutlineGroup... (F12). Select Group byIntervals:Days.

This requires that date column is in Row Fields: box in pivot table layout.

Ref.: http://antilibreoffice.blogspot.com/2... (Rus).

edit flag offensive delete link more

Comments

Thank you!

VirtualMechanic gravatar imageVirtualMechanic ( 2018-11-17 00:06:21 +0200 )edit
1

answered 2018-11-16 23:15:52 +0200

VirtualMechanic gravatar image

updated 2018-11-17 00:05:16 +0200

I parcially resolved the issue. I got the data to show in the Response Time by loading in a fresh .CSV and made sure the 'string delimeter' was empty. For more info, see this FAQ. This did not help me directly, but sent me on the right path.

Non-the-less it still does not compile it per day, but shows all date/time in a list as show above in 'The result' image.

Edit 1: The only way I seem to be able to get the date/time column working as intended is to create an extra column, which only contains a date, not a time.

I do this by inserting 2 columns on the right side of the date/time column. Then copying the date/time column to one column to the right, and splitting it with 'text to column' to split the time to another column. After this I delete the time column I created and use the new 'date' column instead of the original one.

Is there a faster/smarter way?

Edit 2: I found an even easier way to get the date/time format right, ticking the box 'Detect sepcial numbers' when loading in the .CSV. This did the trick for me.

edit flag offensive delete link more

Comments

For cases where the data is already imported as text, there is a dedicated data type conversion tool: Data->Text to Columns. And note that it has column titles right-clickable, giving a useful context menu (just like CSV import dialog).

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-17 08:34:23 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-16 22:55:06 +0200

Seen: 32 times

Last updated: Nov 17 '18