Converting Dates to Days on X Axis in a Chart

Problem

When the date field is used as a category, the category name on the X axis can only be a specific date. How can you convert these dates into days? The following figure shows the effect.

Implementation Method

Two methods are available for converting dates to days:

Method one:  You can directly use the weekday() function in the SQL statement to convert the date.

Method two: When designing the report, set the data dictionary for the chart category. In the data dictionary, use the built-in switch() and weekday() functions to convert the displayed value of the date to weekday.

Note:

This document only demonstrates method two. You can modify the SQL statement to perform method one by yourself.

Data Preparation

1. Create a built-in dataset with two column fields: Date (date type) and Amount (string type), as shown in the following figure.

2. Enter ten records of data in the two columns, as shown in the following figure.

Chart Insertion

Merge  required cells, insert a column chart, and bind the data to the column chart. The following figure shows the detalied setting.

Data Filter Setting

To display the data of a week, set Data Filter to only extract the first seven records of the data, as shown in the following figure.

Data Dictionary Setting

Set Display to Data Dictionaryunder Data Filter > Category. Set Type of Data Dictionary to Formula, and the formula corresponding to Actual Value and Display Value is as follows:

• Actual value: $$$

• Display value: switch(weekday($$$),0,"Sunday",1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday")

Effect Display

1. PC

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

2. Mobile Terminal

The report can be previewed on the DataAnalyst APP and theH5 terminal, as shown in the following figure.

Click to download the template Converting Dates to Days on X Axis in a Chart.cpt.