Hi guys I have a Calc spreadsheet in which I have created a Line Chart with an x axis showing the passage of time in monthly increments starting from the first data point date ie 3rd of December then the next column was 3rd January, then 3rd February and continuing on in this fashion plus a y axis showing numerical values.
I am trying to insert an Secondary x axis that shows the actual date contained within each of the Data Points across the columns of the chart.
I have been able to insert the secondary x axis BUT it is showing the same dates as the primary x axis when I want it to show the dates relating to the various data points across the chart and not the monthly incremental columns.
Does anyone have any advice how I can achieve this outcome please?
Thank you in advance
Thanks to the helpful suggestion by ‘keme’ I have now attached a representative sample spreadsheet with 2 charts contained within. One chart has ‘Show Category’ checked and therefore shows the correct date as a Label and the other has ‘Show value as a number’ checked and shows the correct input value as a Label. What I want to achieve is the DATE data to be shown across the top in a secondary x axis (not as a label) and relative to the physical position of the Data Point within the chart and the correct number values to be retained as Labels in the body of the chart as it is now Plus the primary x axis across the bottom of the chart to show meaningful dates (not these random dates) say at equal monthly spacings to show the related passing of time.
Hopefully that explains it as I await a solution from someone far wiser than myself.
Sample chart x axis problem.ods
Make an X/Y chart with lines.
The “line chart” behaves much like bar/beam charts, and handles the “argument values” (X) as data labels, not coordinate values. This yields even spacing of the data points, not proportionally according to values.
With an X/Y chart, both X and Y values are taken as numerical coordinates.
Edit:
Sample file containing chart with data point X values on a simulated X axis.
Final edit (?)
Your data table has categories down and data series across, so columns and rows change places compared to my previous responses.
Stepwise guide
Explaining how I reached this result based on the uploaded sample file.
- Make a row in your table for the Y position of your “alternate axis”. You can hide this row later if you want.
- I used a formula to automate placement, just above the highest data value.
- Double click the chart to get the tiny black handles. Your chart is in edit mode.
- Right click the chart grid or one of the graphs in it, and select the last item from the context menu,
Data ranges ...or something like that - Select the second tab,
Data series - Click the button to add a data series
- In the top right pane, select
X valuesfrom the list - Set the data range to your row of dates,
$Sheet1.$B$3:$G$3 - Select
Y valuesfrom the list. - Set the data range to point to the new row you just created (hodling Y position for the “artificial” alternate axis)
- Click OK
- Right click the new graph which has been added. Select
Add data labels. - Right click the graph again and select
Format data labels. - Select the
Data labelstab. - Untick
Show valuesand tickShow category - Adjust the label positioning to suit the layout of your chart.
- Select placement centered, above or below
- Rotate label if needed.
- Right click the graph again and select to
Format data series. - Select a line type/color which does not visually interfere with actual data graphs.
I used- none -. - Select an icon for the data point.
I used the last entry inSymbol, a vertical bar.
Thank you keme for your advice.
I have recreated the chart type as X-Y scatter with lines and points BUT I still cannot find a way to make the SECONDARY x axis show the same dates as contained within the various Data Points shown across the chart?
Currently the primary x axis has now changed to seemingly random dates that seem to not relate to the inputted data of the spreadsheet ie the first column of data has a heading of 03/12/19 but the first date on the x axis is now 12/10/19 which precedes the start date of the inputted data so is not featured in the spreadsheet data?
The secondary x axis is identical to the primary and I cannot find a way to change either one of these to reflect the dates contained within the various data Points across the chart?
I am obviously missing something here but I don’t know what it is?
Any help will be much appreciated.
Thank you
Yes, I seem to have misread your question.
See this help page. Does the “Category” item correspond to what you want to display?
Instead of inserting an additional x axis, you could simulate one by creating another line graph (an additional data series) using the same X values and constant Y value. This way you can place your date tags anywhere you like, with a line to represent the simulated axis (or perhaps just as well not), and/or data points for precise marking.
keme thank you for link which I have had a look at but I am none the wiser unfortunately although I was able to add the numeric value of the Data Points onto the chart adjoining each Data Point so that is helpful.
What I am attempting to achieve is the primary x axis showing monthly dates evenly spaced on the corresponding vertical grid lines PLUS the secondary x axis showing the date of occurrence of each of the separate Data Points. I can see the dates contained in the Data Points when I hover the mouse over the individual Data Point and it is there following the word “values” along with the numeric value as well?
Is what I am trying to do even possible???
I am using 7.0.3.1 Libre Office
keme yes your attachment is what I am seeking to achieve. I note that the top dates are shown as Labels.
Am I able to insert these labels into the top of my original chart and delete the secondary x axis?
I am trying to figure it out but maybe you could advise just how you did this?
Thank you for your ongoing help
The process is a bit convoluted. Roughly, and by memory (not at my regular computer right now) :
- Add a “mock” data series to represent the secondary axis.
- Double click the chart so it gets the tiny black dot handles (not the larger square colored handles). That means you are in chart edit mode.
- Right click your existing graph line to add a data series (menu item “data ranges” or something like that).
- Use your mock data with the old x values to create the graph emulating a secondary axis
- Right click the new graph/axis/whateveryoucallit to add data labels.
- Right click again to adjust (or perhaps “format”) the labels
- Tick “Category”
- untick “Values”
I think that’s about it. Make adjustments for rotated text, colors, etc. See if you can make it work. I’ll look into the details later if you need me to (unless someone else will come to your rescue). Can’t do it right now. Sorry!
keme thank you once again for having a go at this. I am unfortunately unable to make it work. I can generate the chart with the legend and all the actual values inserted into the chart adjacent to the individual Data Points BUT both the primary and secondary x axes resolve to unusual date that have not been inserted into any of the data in the spreadsheet that then resolves to the chart. I cannot find any way to make either of these axes reflect the date values that are in the spreadsheet and are also visible in the Data Points within the charts when the mouse hovers over a Data Point.
So I am at a loss to know what to try from here. I continue to Google in an effort to find a solution but surely it cannot be this difficult to do what is a basic and simple requirement???
Any further ideas from yourself or indeed anyone will be much welcomed.
Thanks keme
Edit your original question to attach a file with the general structure of your data, and also containing your chart.
A copy of your working file, preferably, but remember that this is a public place, so if content is confidential, replace by mangled/random content.
Seeing the actual data, it is easier to create a working solution, and to give an explanation which fits the context.
keme I have just posted the file as you suggested. Thanks
Hi guys does anyone have a solution to this little problem please? Please see the attached file if you wish to try and assist. Thank you
Anyone? Looking for help to solve this please.
Is there no one who can shed some light on this vexing problem?
Anyone willing to help please as I need to solve this somehow?
Sorry for taking so long. I have been interrupted a couple of times and had to start over.
Finally, a step by step guide has been added to the answer entry above (too long to fit into a single comment). Hopefully you find your solution there somewhere, either by going through the moves as outlined (recommended) or by “recycling” the edited chart in the attached file.
I strongly urge you to follow the stepwise procedure and create this for yourself, even if the existing chart can be used over (I dont know whether that is at all possible). Quite a few steps, but mostly logical once you “connect the dots”. It will be useful for other cases as well.
keme I want to thank you so much for taking the time and effort to put together the step by step instructions to solving my problem. I was eventually able to follow them and apply them to my sample file and then also to my actual spreadsheet that this all started from. It took me a while to understand the basis of what I was doing but now I think I understand. It appears that your approach is a ‘workaround’ for a shortcoming in the capability of Libre Calc to simply populate the secondary x axis with the existing inputted dates as it should be able to do ( I am not sure why this is this way?)
I was confused by your inputting the number 22 into a row of cells but then the lightbulb went off in my head and I began to understand your unique approach.
Well done to you and I remain appreciative of your continued efforts in providing a solution to this annoying oversight in Libre’s basic functions.
I have marked your answer as correct and solved.
Regards
Wayne