Issue 128448 - Chart data labels as categories show a third variable, e.g. dates.
Summary: Chart data labels as categories show a third variable, e.g. dates.
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: 4.1.9
Hardware: PC Windows 10
: P5 (lowest) Minor (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: usability
Depends on:
Reported: 2021-04-16 01:35 UTC by jsteidel
Modified: 2021-04-21 14:50 UTC (History)
1 user (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description jsteidel 2021-04-16 01:35:33 UTC
I wanted to plot XY data and also show the date with each data point (as a label).  The data represents the number of items sold at a Farmers' market tent, the outdoor temperature, and the date.  Visitor counts could substitute easily in this scenario.

Date values used as categories show on plots as the date numbers.  The Chart wizard does not allow reformatting of category labels as numbers as it does for the (Y) values.  

A new button for that would be nice, but I found a workaround to reformat the dates as text in the spreadsheet body and then use that text for the labels.  The workaround requires a second row (column) for dates as text to use as category labels.  Here's how:

Sample data in three rows.  Copy the data below and paste to the first three rows in a Calc spreadsheet with these options:   
Separated by [check] comma, 
Other Options [check] detect special numbers.  
Click OK.

count      ,84,71,87,78,70,89,77,85,82,78,89
date (date format),01/23/21,01/30/21,02/06/21,02/27/21,03/06/21,03/13/21,03/20/21,03/27/21, 04/03/21,04/10/21,04/17/21

Enter the formula in the cell under the first date, e.g. in cell B4 use  =TEXT(B3;"MM/DD")   to reformat the date into a text value.  Copy and paste the formula for the remaining dates.  Get the dates reformatted at text:
	01/23	01/30	02/06	02/27	03/06	03/13	03/20	03/27	04/03	04/10	04/17

Highlight the first two rows of data including descriptions.
Click the Chart button.  
1 Chart Type: Choose XY (Scatter), points only, Next
2 Data Range: Choose Data series in rows, uncheck First row as label and check First column as label. Next.
3 Data Series:
See data series 'count' with row 1 for X and row 2 for Y.
Click in the Data labels field, click the Select data range button to the right,
and select the cells with text dates, e.g. in row 4.
(Keep the 'count' series, and Remove any other data series.)  Next.
Enter for the X axis:  temperature °F
Enter for the Y axis:  count
Uncheck Display legend.  Finish.

Right-click the Y axis, Format Axis.  On the Scale tab uncheck Automatic for the Minimum and choose a number so the points fill the area, e.g. 65.  OK.

Right-click a point of the data series, choose Insert Data Labels.
The default data labels are the Y values.

Right-click a point again, choose Format Data Labels.
Uncheck Show value as a number.  Check Show category. Set Placement to Below.  Click OK.

Results:  The cells of text converted from the date are used as labels in the user's choice of date format.

At first I didn't realize the 
Format Data Labels dialog: Data Labels tab: 'categories' field 
is the Data Ranges dialog: Data Series tab: 'Data Labels' field 

It took a little while to find and use the TEXT function, but it was there--Thank you!

The data can be plotted with values and categories in the same position by the data points, above or below.  The same data can be plotted and display the value as a number in one placement (e.g. above the data point) and the same data can be plotted again with the category other in a different placement (e.g. below the data point).

Hiding the row of dates as text makes the category labels default to the sequence number of the point.  Hiding the row of dates as date values makes it take another step to change dates.  So it's easier to have the two rows of dates.

It would look nicer using just the one row with formatted date values, but I realize that the new feature would take development effort and could affect compatibilities with other spreadsheets and applications.

Thank you.
Comment 1 oooforum (fr) 2021-04-21 14:50:40 UTC
Save our time and provide :
1) a sample document in ODS format
2) 2 screenshots for expected/observed situation