[Solved] No data in pivot table

Discuss the spreadsheet application
Post Reply
McBryde
Posts: 6
Joined: Sat Apr 17, 2021 5:55 pm

[Solved] No data in pivot table

Post by McBryde »

Hello somebody helpful [I hope...].

My pivot table stopped working for no clear reason. It no longer displays the numbers.
I use it to organise my timesheets into clients, projects, description and duration. If you look at the attached screenshot you'll see that none of the times show up.I don't know what I'm doing differently this time, if anything.
Screenshot 2021-04-17 at 17.01.10.png
Thanks,
John
Last edited by robleyd on Tue Apr 20, 2021 1:44 am, edited 1 time in total.
Reason: Tagged [Solved]; add green tick
LibreOffice 7.0.4.2 Mac OS 10.16.7
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: No data in pivot table

Post by MrProgrammer »

Hi, and welcome to the forum.
McBryde wrote:… you'll see that none of the times show up.
Perhaps your times in the Duration column are text values. This can be due to an inappropriate data import. You've chosen SUM as the aggregation function for Duration so text values are ignored; you would need re-import the data correctly or convert the text to times. Read sections 1. Types of data and 4. Times in cells in Ten concepts that every Calc user should know. If you are importing data into your spreadsheet read [Tutorial] Text to Columns
McBryde wrote:If you look at the attached screenshot …
For any additional assistance attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
McBryde
Posts: 6
Joined: Sat Apr 17, 2021 5:55 pm

Re: No data in pivot table

Post by McBryde »

Thank you for your attention, Mr Programmer.
I think I had the date import setting ok [see screenshot].
Also, I had checked the formatting of the time and the date cells.
I attach the file, and a screenshot of the options in the pivot table [the latter i case I have them wrong].

I think I'm doing everything the way I used to, when the numbers were showing up. But, you never know with the human brain...
_ John
Attachments
Screenshot 2021-04-19 at 13.58.47.png
Screenshot 2021-04-19 at 14.02.04.png
Toggl_time_entries_2020-07-11_to_2021-04-16.csv
(6.53 KiB) Downloaded 127 times
LibreOffice 7.0.4.2 Mac OS 10.16.7
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: No data in pivot table

Post by John_Ha »

Please upload the spreadsheet .ods file - the .csv file does not contain the pivot table.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
McBryde
Posts: 6
Joined: Sat Apr 17, 2021 5:55 pm

Re: No data in pivot table

Post by McBryde »

Here it is:
Attachments
Toggl_time_entries_2020-07-11_to_2021-04-16.ods
(26.33 KiB) Downloaded 126 times
LibreOffice 7.0.4.2 Mac OS 10.16.7
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: No data in pivot table

Post by MrProgrammer »

McBryde wrote:Here it is: Toggl_time_entries_2020-07-11_to_2021-04-16.ods
Thank you for the attachment. I could not locate a pivot table in it but it shows why you are having trouble.
McBryde wrote:I had checked the formatting of the time and the date cells.
The cell formatting is not relevant. What matters are the cell values. Formatting and values are independent cell attributes. Identical values in two cells can be formatted to display differently. Or different values in two cells can be formatted to display identically. Study the Ten Concepts tutorial.

Your Duration is column K in your attachment. There are no numbers in that column, only text. For example, cell K2 contains the six characters "0.12 h". SUM ignores text, so your pivot table is empty. You can use Format → Cells → Numbers to change the numeric cell format of K2 all day and it will not help your pivot table because numeric formatting does not apply to text and does not change the value of the cell. Formatting never changes the values in cells.

If you use Edit → Find & Replace to remove the "h" in column K, you will have numbers which, presumably, represent fractions of an hour. Those you can sum. You still will not have Calc times/durations though, because times/durations are fractions of a day, however the resulting pivot table may be suitable for you. Or you could replace the text in K2 with =I2+J2-G2-H2 and fill that formula down to K54. Then you will have Calc durations (study the tutorial) in that column. I don't know if that calculation would be appropriate here since it's your data.
[Tutorial] How do I specify the formula for a column?

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
McBryde
Posts: 6
Joined: Sat Apr 17, 2021 5:55 pm

Re: No data in pivot table

Post by McBryde »

I appreciate your help, and will go through your answer soon. :)
LibreOffice 7.0.4.2 Mac OS 10.16.7
McBryde
Posts: 6
Joined: Sat Apr 17, 2021 5:55 pm

Re: No data in pivot table

Post by McBryde »

I understand you. Once or twice I would get a ' mark in front of a number and found I could remove it that way [find etc]. Later I realised it was coming out as text because I didn't check the special character box.
In this case, though, I don't know why it stuck an 'h' at the end of it.

I also follow your other suggestion, and will try that if I don't manage to get rid of that 'h'.
It's good to have an understanding what's going on.
LibreOffice 7.0.4.2 Mac OS 10.16.7
McBryde
Posts: 6
Joined: Sat Apr 17, 2021 5:55 pm

Re: No data in pivot table

Post by McBryde »

I managed to find and replace the 'h' with '0' and I have it working now .... which is great!
This has been bugging me for a few days.
Thank you very much for your help.
LibreOffice 7.0.4.2 Mac OS 10.16.7
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: No data in pivot table

Post by John_Ha »

McBryde wrote:In this case, though, I don't know why it stuck an 'h' at the end of it.
It is the "h" (possibly as an abbreviation for hour?) you imported from the .csv file.

The presence of the "space h" in the item "0.18 h" means that value is brought in as a character string of six characters, and not brought in as a number.

Ideally, change the way the .csv file is created so it does not have the "space h" or edit out the "space h" from the .csv file.
Attachments
Clipboard01.gif
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Post Reply