Calc chart x-axis labels

users
#1

I think this is probably by design. When you plot a chart, you don't normally put individual data values on the axes, but merely label the axis with round, equally spaced values. Since your data is roughly monthly, having x-values labelled once per month sounds sensible. Note that the y-axis is not labelled with your actual balance amounts but likewise with suitably round, equally spaced values.

A column chart appears to set out the columns somewhat regularly, not exactly according to the x-value for each column. Perhaps that is because such charts are normally used for non-parametric statistics, where this makes sense. And you shouldn't really want your equally spaced columns to be labelled with unequally spaced vales.

So what can you do? One possibility is to use a chart of type "X-Y (Scatter)", where the x-axis labels will still be equally spaced, but the data points will be displaced into their appropriate place within each month. But I suspect a better solution, if you really want a column chart, is to label your columns with just the relevant month - discarding the day information. You can do this either by changing the format of your column of statement dates and keeping your "Source format" option, or - probably more sensibly - to keep the original dates showing in your table but to untick "Source format" in the x-Axis dialogue and set a suitable date format there. How about something like "MM/YY" (12/14) or "MMM YY" (Dec 14)?

Alternatively, you can use Insert Data Labels to create labels on the columns themselves. Use Format Data Labels... to adjust them; you can choose "Show category" to place the date values instead of the balance amounts on the columns. Format the dates as you wish (they inherit the Number format from the x-Axis dialogue), possibly rotating them to fit the columns. Once you have the data labels as you want them - perhaps "D MMM YYYY" (2 Dec 2014) - you can choose to delete the x-axis entirely, so you just have columns labelled with the actual dates.

I trust this helps.

Brian Barker

#2

Brian Barker wrote

I have a table containing data on my credit card. Left-most column
is the monthly statement dates, (oldest to newest, top to bottom)
which are not always the same. The next column to the right is the
monthly balance. When I create a Column Type chart from these two
columns of data, I have the balances as the Y data, and the dates as
the X column labels. For the X column labels, Calc always sets the
dates to the first day of the month, not the real (table) date.

I have checked the "Format Axis..." settings, and on the Numbers
tab, "Source Format" is checked. I think this means that it is
supposed to set the x-axis labels to the format of the cells in the
table. But, this is not happening. All of the x-axis labels are set
to the first day of the month, i.e. 01/01/14, instead of 01/24/14
(the date in the table).

I have tried all of the other chart types, none of them source the
dates correctly. Is this a bug, or am I missing something?

I think this is probably by design. When you plot a chart, you don't
normally put individual data values on the axes, but merely label the
axis with round, equally spaced values. Since your data is roughly
monthly, having x-values labelled once per month sounds sensible.
Note that the y-axis is not labelled with your actual balance amounts
but likewise with suitably round, equally spaced values.

I never thought about it this way, makes sense though.

A column chart appears to set out the columns somewhat regularly, not
exactly according to the x-value for each column. Perhaps that is
because such charts are normally used for non-parametric statistics,
where this makes sense. And you shouldn't really want your equally
spaced columns to be labelled with unequally spaced vales.

Correct, but I thought with "Source Format" selected it would use the source
format as is.

So what can you do? One possibility is to use a chart of type "X-Y
(Scatter)", where the x-axis labels will still be equally spaced, but
the data points will be displaced into their appropriate place within
each month. But I suspect a better solution, if you really want a
column chart, is to label your columns with just the relevant month -
discarding the day information. You can do this either by changing
the format of your column of statement dates and keeping your "Source
format" option, or - probably more sensibly - to keep the original
dates showing in your table but to untick "Source format" in the
x-Axis dialogue and set a suitable date format there. How about
something like "MM/YY" (12/14) or "MMM YY" (Dec 14)?

I tried the "X-Y Scatter" chart type; it still didn't give me what I wanted,
and I don't like the "graph" line.

Alternatively, you can use Insert Data Labels to create labels on the
columns themselves. Use Format Data Labels... to adjust them; you can
choose "Show category" to place the date values instead of the
balance amounts on the columns. Format the dates as you wish (they
inherit the Number format from the x-Axis dialogue), possibly
rotating them to fit the columns. Once you have the data labels as
you want them - perhaps "D MMM YYYY" (2 Dec 2014) - you can choose to
delete the x-axis entirely, so you just have columns labelled with
the actual dates.

Lots of good ideas Brian, thank you for your input.

#3

... I thought with "Source Format" selected it would use the source format as is.

It does: if your original format, say, is DD/MM/YY, then that is how the axis values will appear. It keeps the format, not the values.

I tried the "X-Y Scatter" chart type; it still didn't give me what I wanted, and I don't like the "graph" line.

You can have X-Y Scatter without the line if you prefer.

Brian Barker

#4

Brian Barker wrote