Apache OpenOffice Community Forum

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

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.

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

Please upload the spreadsheet .ods file - the .csv file does not contain the pivot table.

Here it is:

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.

I appreciate your help, and will go through your answer soon.

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.

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.

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.