Format the Y Axis Label to Date

1. Problem description

In some scenarios, the Y-axis of the chart is date-based data from the data set, and the axis labels of the Y-axis also need to be modified to the date format. Take the line chart as an example, the effect is as shown in the figure below:

If you just change the axis label under chart Style>Axis>Y Axis>Format to Date.

When previewing on the front end, the chart data cannot be displayed normally, as shown in the following figure:

So how do you set it to change the axis label to the date format and to ensure that the chart displays the data normally? It will be described in detail below.

2. Solution

1) Drag the date data corresponding to the Y axis in the data set into the cell, and use the DATETONUMBER(date) formula to convert it to milliseconds.

2) The chart binding has been converted to the cell data in milliseconds.

3) Finally, modify the axis label of the Y-axis of the chart to a date type.

Note: For the introduction of the DATETONUMBER(date) formula, please refer to the document: Date and time function

1. Prepare data

Create a New General Report, create a new built-in dataset, and modify the name of the dataset to ds1, as shown in the following figure:

The Craft field in the dataset is a string type, corresponding to the classification of the X axis. The Schedule field is a date type, corresponding to the value of the Y axis.

2. Insert chart

Merge a range of cells, select the merged cell A1, click the Insert Chart shortcut button to insert a line chart, as shown in the figure below:

3. Date to milliseconds

Drag the Craft and Schedule fields from the built-in data set to cells A22 and B22 respectively, select cell D22, click the shortcut button of Insert Formula, and insert the formula: DATETONUMBER(B22), as shown in the figure below:

4. Binding data

In the properties panel of the line chart, select Cell Attributes>Data, select the cell data from the drop-down list of data sources, set the category name to cell A22, add a new series with an empty series name, and set the value to cell D22, as shown in the figure below:

5. Setting the style

1) Title

In the line chart properties panel, select Cell Attributes>Style>Title, check Show Title, the title content is: Semiconductor Process Optimization Scheduling, as shown in the following figure:

2) Coordinate axis

In the properties panel of the line chart, select Cell Attributes>Style>Axis>Y Axis, and the minimum value definition is: 1262275200000, which means that the minimum axis label is 2011-03-13. Modify the axis label to date, as shown in the following figure:

Note: The purpose of setting the minimum value is to allow the value of the Y axis to have a reasonable interval and make the trend of the line chart more obvious.

3) Tooltip

In the properties panel of the line chart, select Cell Attributes> Style> Tooltip, check Use Tooltip, select Category name, Series and Value, and set the value format to date, as shown in the following figure:

6. Hide cells

Select the 22nd row, right-click and choose Hide, the cell data will not be displayed in the front end, as shown in the following figure:

7. Effect preview

1) PC

Save the report, click the Pagination Preview, the effect is as shown in the figure below:

2) Mobile

See the completed template:%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Primary\Chart\Format the Y Axis Label to Date.cpt

Click to download the template: 

Format the Y Axis Label to Date.cpt