Label in Date Format on Y Axis

Problem

In some scenarios, as the values on the Y axis of a chart is the date data from a dataset,  the axis labels of the Y axis also need to be modified to the date format. Take a line chart as an example. The following figure shows the effect.

If you only set the format of axis labels in the chart to Date under Style > Axis > Y Axis > Format,

 the chart data cannot be displayed properly in the front-end preview, as shown in the following figure.

So how to set the format of axis labels to date format while ensuring  that the chart displays the data properly? This document will introduce the solution in detail.

Solution

1. Drag the date data corresponding to the Y axis in the dataset into a cell and convert the data to milliseconds by using the formula DATETONUMBER(date).

2. Bind   the cell data converted to milliseconds to the chart.

3. Finally, set Axis Label Format of the Y axis of the chart to Date.

Data Preparation

Create a general report, create a built-in dataset, and modify the dataset name to ds1, as shown in the following figure.

The Process field in the dataset is the string type, corresponding to the X axis category. The Scheduling field is the date type, corresponding to the Y axis value.

Chart Insertion

Merge  required cells, select the merged cell A1, click the Insert Chart icon, insert Line Chart, and click OK, as shown in the following figure.

Converting Date to Milliseconds

Drag the Process and Scheduling fields from the built-in dataset into cells A22 and cell B22 respectively, select cell D22, click the Insert Formula icon, and insert the formula DATETONUMBER(B22), as shown in the following figure.

Data Binding

On the line chart attribute panel, choose Cell Element > Data, select Cell Data from the drop-down list of Data Source, set the category name to cell A22, add a series with an empty series name, and set the value to cell D22, as shown in the following figure.

Style Setting

Title

On the line chart attribute panel, choose Cell Element > Style > Title, select Show Title, and set the title content to Semiconductor Process Scheduling, as shown in the following figure.

Axis

On the line chart attribute panel, choose Cell Element > Style > Axis > Y Axis, tick Custom Minimum Value in Value Definition, set the value of Minimum to1262275200000, which means the minimum axis label is 2010-01-01, and set Axis Label Format to Date, as shown in the following figure.

Note:

The purpose of setting the minimum value is to set a reasonable range for the values on the Y axis , making the  line trend in the chart more obvious. You can use the DATETONUMBER(date) formula to convert the date as needed.

Prompt

On the line chart attribute panel, choose Cell Element > Style > Prompt, select Use Tooltip, select Category Name, Series, and Value, and set Format of Value to Date, as shown in the following figure.

Cell Hiding

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

Effect Display

PC

Save the report, and click Pagination Preview. The following figure shows the preview effect.

Note:

The Y axis label value is set to 2010-01-01, but the chart scale will automatically display the content from an appropriate value , so the minimum value displayed is not the set minimum value.

Mobile Terminal

Click to download the template Label in Date Format on Y Axis.cpt.