[Solved] No data in pivot table
[Solved] No data in pivot table
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. Thanks,
John
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. 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
Reason: Tagged [Solved]; add green tick
LibreOffice 7.0.4.2 Mac OS 10.16.7
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: No data in pivot table
Hi, and welcome to the forum.
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.
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 ColumnsMcBryde wrote:… you'll see that none of the times show up.
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).McBryde wrote:If you look at the attached screenshot …
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).
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).
Re: No data in pivot table
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
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
-
- Toggl_time_entries_2020-07-11_to_2021-04-16.csv
- (6.53 KiB) Downloaded 142 times
LibreOffice 7.0.4.2 Mac OS 10.16.7
Re: No data in pivot table
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.
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.
Re: No data in pivot table
Here it is:
- Attachments
-
- Toggl_time_entries_2020-07-11_to_2021-04-16.ods
- (26.33 KiB) Downloaded 144 times
LibreOffice 7.0.4.2 Mac OS 10.16.7
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: No data in pivot table
Thank you for the attachment. I could not locate a pivot table in it but it shows why you are having trouble.McBryde wrote:Here it is: Toggl_time_entries_2020-07-11_to_2021-04-16.ods
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.McBryde wrote:I had checked the formatting of the time and the date cells.
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).
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).
Re: No data in pivot table
I appreciate your help, and will go through your answer soon.
LibreOffice 7.0.4.2 Mac OS 10.16.7
Re: No data in pivot table
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.
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
Re: No data in pivot table
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.
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
Re: No data in pivot table
It is the "h" (possibly as an abbreviation for hour?) you imported from the .csv file.McBryde wrote:In this case, though, I don't know why it stuck an 'h' at the end of it.
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.
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.
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.