[Solved] Calc inserts skipped dates on x-axis

[appsman]

Greetings. I am trying to plot a chart with data for certain dates. I do not want to include the dates with no data. In the attached figures, I don't want to show the dates 29AUG2017~15SEP2017. In fact, the columns don't exist at all in the spreadsheet!

data

Screen Shot 2017-09-16 at 5.16.27 AM.png (20.94 KiB) Viewed 2956 times

In other words, I need to plot like a "line chart" not a "scatter chart" in Windows.

Is it possible?

[Zizi64]

In other words, I need to plot like a "line chart" not a "scatter chart" in Windows.
Is it possible?

Yes, it is.
Just use the Line (or Column) type of graphs instead of the Scatter type, when you create a chart. Only the Scatter typp can draw an X axis with proportional values. All of others draw an X axis with evenly distributed values.

And you can modify the type of the existing chart: Double click on the chart - Right click on the chart - Diagram type.


Can you upload your ODF type sample file here?

[appsman]

Thanks for the help. In fact, I *did* create the chart as a column chart but it still created the "phantom" spaces on the x-axis.

One note: this is an *.ods (attached) from LibreOffice, not OpenOffice.

[jrkrideau]

A quick look suggests that you are graphing Row 2 as the x-axis. Therefore every date is included in the line-chart.

You would have to remove all the dates with no data in its column to remove them in the chart.

[appsman]

Thanks for the attention. However, please take another look. There is *no* data for the dates 29AUG, 30AUG, ... 15SEP, yet the chart still plots the dates!

I was on vacation for those dates and it really screws up the chart having a big empty space.

[jrkrideau]

There is *no* data for the dates 29AUG, 30AUG, ... 15SEP, yet the chart still plots the dates!

Yes, that is what I was trying to say before. My understanding of how a "column" chart works is that each entry in the x-axis data range is treated as a character value and is inserted into the axis. Calc does not care if you have 24Aug17, 25Aug17, 26Aug17, 27Aug17or A, B, C, D as the x-axis. Every one of those entries is going to show up in the chart. Calc is ignoring the fact that you have 24Aug17, 25Aug17, etc. formatted as dates and is treating them as labels.


I still think that you need to remove those dates with no data.

[RusselB]

A quick look at the spreadsheet, and the accompanying graph shows to me, quite clearly what appsman is talking about.
It's not really that the graph is showing entries for dates that have no data, but rather the fact that the generated graph is showing dates that don't exist in the range specified for the graph.

[appsman]

Yes! Thanks, finally someone has understood.

the generated graph is showing dates that don't exist in the range specified for the graph.

This has the problem exactly backwards:

Calc does not care if you have 24Aug17, 25Aug17, 26Aug17, 27Aug17or A, B, C, D as the x-axis.... Calc is ignoring the fact that you have 24Aug17, 25Aug17, etc. formatted as dates and is treating them as labels.

Calc is treating the x-axis values as "numbers" (dates in my case) and inserting dates that do *not* exist in the spreadsheet.

I need the chart to treat x-axis vales as labels.

[RusselB]

I can think of two options.
1) Make your entries for the dates as text entries.
2) Add a helper row that uses a formula to return the text equivalent of the date that you are currently using for your X axis.
Either of these has complications, but that's the best I can suggest.

[appsman]

Thanks. This solves it! I require my dates to be manipulated as actual date/number values, so I chose option (2).

Add a helper row that uses a formula to return the text equivalent of the date that you are currently using for your X axis.

Here is how I converted date to text: First place the date-formatted number in row 99.

Code: Select all

=TEXT(D99,"DDMMMYY")

[jrkrideau]

Ah, so that's what I missed.

I think LO is doing something differently than AOO and I completely misinterpreted what was happening. Sorry.