[Solved] Too many x-axis labels and need to reduce

Discuss the spreadsheet application
Post Reply
joniverson
Posts: 8
Joined: Mon Mar 29, 2021 5:09 am

[Solved] Too many x-axis labels and need to reduce

Post by joniverson »

:( Hi,
New to open office. The best way I can explain the problem I'm having is with a screencapture video I made:

https://youtu.be/OTz8QlSuGbA

Been trying to figure it out for hours, but still can't seem to make the x-axis values less jumbled and look better.

Thanks in advance for your help,
Jon
OpenOffice 4.1.9 on Ubuntu 18.04
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: too many x-axis labels and need to reduce

Post by FJCC »

You need to use a scatter plot instead of a line plot. A scatter plot treats the x values as numbers so you can set the scale to have tick marks very N days or whatever is appropriate. A line plot treats the x axis as categories and it prints every one and that is what is causing your problem.
You probably also have to combine columns A and B to form a single timestamp for each row. I can't tell what is being chosen for the x axis when you select three columns to plot but I suspect it will not result in the scatter plot that you want.
If you upload the file, people could make more specific suggestions. To do that, click PostReply and look for the Upload Attachment tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
joniverson
Posts: 8
Joined: Mon Mar 29, 2021 5:09 am

Re: Too many x-axis labels and need to reduce

Post by joniverson »

Thanks. I actually did try scatter plot and it did indeed offer spaced values but none of the x-axis values matched the times so it might be a lot of work to change them.
I'm trying to keep everything as automated as possible as I intend to use the program to make monthly charts of the background radiation data collected. Drag and drop with as minimal intervention as possible and create the chart. With the data points in the CSV text file so far, there are only 577. That's only just over nine hours since samples are taken once each min. Over a month, there will be over 43,000 for the x-axis!

Well, I'll attach the same text file I used with the CSV values. You have the date and time of the sample recorded (one per minute) on the left side, then CPM or counts per minute from the detector in the center, and then finally uR/h, a unit of radiation, on the right. My desire is to use the first two columns on the left (date and time) plus the uR/h values on the right.

Time and date on the x-axis and uR/h on y.

Thanks in advance for any help.
Attachments
UGP-data.txt
(31.55 KiB) Downloaded 154 times
OpenOffice 4.1.9 on Ubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Too many x-axis labels and need to reduce

Post by Villeroy »

This is a text file. A text file is not a spreadsheet by any means. You have to import text with proper import options.
Did you import with an adequate language setting for the decimal points (e.g. English)? If not, your decimal figures are imported as text.
Did you check "Detect special numbers"? No? Then your time stamps in column #1 are imported as text.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Too many x-axis labels and need to reduce

Post by Zizi64 »

Use the comma (,) and the TAB as data separator in the import filter. Then the timestamps (the combined date-time numeric values) will be imported into one column.

Fortunately the dates have ISO format
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
joniverson
Posts: 8
Joined: Mon Mar 29, 2021 5:09 am

Re: Too many x-axis labels and need to reduce

Post by joniverson »

Attached is a screen grab of what I did after dragging and dropping the txt file in OOC. After that, it appears as in the video above.
Attachments
dragndroppopup.jpg
OpenOffice 4.1.9 on Ubuntu 18.04
joniverson
Posts: 8
Joined: Mon Mar 29, 2021 5:09 am

Re: Too many x-axis labels and need to reduce

Post by joniverson »

I'll try the import changes suggested and see what happens. Thanks.
OpenOffice 4.1.9 on Ubuntu 18.04
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Too many x-axis labels and need to reduce

Post by John_Ha »

Try something like this.

I converted the hours, minutes and seconds into fractions of an hour; and plotted that against the values. I started at 00:00:00.

There is a gap in your data I highlighted in yellow. I should have used Conditional Formatting to "set background to yellow if delta is greater that 1.5 minutes".

You need to tidy up by adding the date and legends to the axes etc.
graph.png
Attachments
UGP-data.ods
(70.58 KiB) Downloaded 152 times
Last edited by John_Ha on Mon Mar 29, 2021 8:59 pm, edited 1 time in total.
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Too many x-axis labels and need to reduce

Post by Villeroy »

After the import hit Ctrl+F8 (value highlighting). The dates and decimals should be displayed in blue font indicating numbers.
 Edit: John's conversion works although he imported text dates. It works because your text dates follow the ISO standard (YYYY-MM-DD HH:MM:SS). The conversion would fail with any other text format which is why I recommend to check the "special numbers" option because this will import proper numeric values from a wide range of date formats. With proper numbers you could also use the formula =A2*24 for the fractions of hours because spreadsheet dates and times represent days and fractions of days. 
.
 Edit: John's formula will fail when there is a date change at midnight 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
joniverson
Posts: 8
Joined: Mon Mar 29, 2021 5:09 am

Re: Too many x-axis labels and need to reduce

Post by joniverson »

Attached is a very acceptable effort, but two questions remain:
1) How do I export just the graph from within Calc and
2) John_Ha, how do I use Conditional Formatting to "set background to yellow if delta is greater that 1.5 minutes"?

Thanks again!
Attachments
graph.jpg
OpenOffice 4.1.9 on Ubuntu 18.04
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Too many x-axis labels and need to reduce

Post by John_Ha »

Villeroy

Agreed. In my mind I thought of doing one per graph per day. As the readings are taken at 1 minute intervals that is 1,440 points - quite enough for a graph.
joniverson wrote: 1) How do I export just the graph from within Calc
Select the graph. File > Export > Select PDF > Selection ..., creates a PDF. See See [Tutorial]How do I view or edit a PDF file with OpenOffice? for how to get very high quality images out of PDF files although it seems the AOO graph is not created as an image in the PDF.

I would use PrtScr to copy the screen to the clipboard - it's how I created my image. Paste into an image editor and edit as required. Save as a PNG file, not as a JPG because a JPG blurs everything. See [Tutorial] Some useful hints on using images.
 Edit: LibreOffice Calc has File > Export > Select PNG .., where a PNG file can be created. 
joniverson wrote: how do I use Conditional Formatting to "set background to yellow if delta is greater that 1.5 minutes"?
I would have to look up the manual or Help, or search the forum, so you might as well do it :-)
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.
joniverson
Posts: 8
Joined: Mon Mar 29, 2021 5:09 am

Re: Too many x-axis labels and need to reduce

Post by joniverson »

Thanks again to all for the help. I jotted down the instructions on a separate sheet of paper since I'll forget the process in between the months most likely.
Only one issue remains. On my Ubuntu 18.04 system, I'm noticing that OOC is sometimes freezing when I'm trying to rescale the graphing. I have to end up using the "xkill" command to shut it down. Not sure what that's about. I can create a graph, although it takes about 20 times longer, on the old PII XP system where the radiation data is collected and no freezes there.
OpenOffice 4.1.9 on Ubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Too many x-axis labels and need to reduce

Post by Villeroy »

Since 3 years I use the LibreOffice which came preinstalled with Ubuntu 18.4 without any problems. I installed AOO just as a reference for this forum but I can't use it for my daily work because it freezes with most of my documents. AOO is no longer under active development so it does not make me wonder that it runs very stable under Windows XP.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
joniverson
Posts: 8
Joined: Mon Mar 29, 2021 5:09 am

Re: Too many x-axis labels and need to reduce

Post by joniverson »

That's too bad. I had hoped to keep OOC since Libre only works with Linux, AFAIK. That way, I wouldn't have to worry about compatibility when switching between OS's.
OpenOffice 4.1.9 on Ubuntu 18.04
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Too many x-axis labels and need to reduce

Post by John_Ha »

joniverson wrote:I had hoped to keep [AOO] since Libre only works with Linux, AFAIK.
LO works on Windows and is far more developed than AOO - for example, it handles much larger files.

By default, and as installed, LO version 7 saves files in ODF Version 1.3 Extended.

AOO does not support ODF 1.3 and gives a warning when opening a 1.3 file so I would recommend changing LO to save in version 1.2 Extended, the same as AOO. Tools > Options > Load/Save.
Last edited by John_Ha on Tue Mar 30, 2021 5:08 pm, edited 1 time in total.
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Too many x-axis labels and need to reduce

Post by Villeroy »

If LibreOffice worked on Linux only, you could use it anyway. The point of all this is not the software. It is the file format which makes the difference. Dozends of software products can read and write these files, including MS Office. This is because the file format is free by any means, fully documented and just as complex as necessary.
LibreOffice has been forked from OpenOffice 3.3 in 2011. Apart from the branding versions 3.3 of both programs were identical. Of course, LibreOffice is availlable for all platforms. But more importantly, it uses the same Open Document Format file as OpenOffice.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply