Dynamic Carousel in a Bar Chart

Problem

When the data source contains the category, series, and time dimensions, you may want to rotate the chart data in the ascending order of time or rotate multiple columns for viewing, as shown in the following figure.

Implementation Method

Set a time parameter in the SQL query statement to filter the data. The value of the time parameter will change with time and then the chart will be refreshed regularly.

Data Preparation

Create a general report and create a dataset ds1. The SQL statement is as follows:

SELECT

`Shipper's City`,

strftime('%Y-%m',"Order Date") Date,

sum("Shipping cost") "Shipping Cost"

FROM Orders

where strftime('%Y-%m', "Order Date")='${left(monthdelta("2010-06-10",mod((datetonumber(now())-datetonumber(a))/1000,12)),7)}'

group by `Shipper's City`,strftime('%Y-%m', "Order Date")

order by "Shipping cost"

Report Design

1. Choose Template > Template Parameter in the menu bar. Add a new parameter, double-click the parameter, rename it a, and set Default Value to =NOW(), as shown in the following figure.

2. Select the merged cell and click the Insert Chart icon on the upper toolbar to insert a bar chart. Click Data and configure the specific settings as shown in the following figure.

=

3. Choose Style > Title, tick Show Title, and enter =UNIQUEARRAY(ds1.select("Date")) in the title content. Click Custom Floating Position, set X Direction to 50 and Y Direction to 50 in the Distance to Upper Left Corner setting pop-up box, and set the font size to 26.

Note:

The entered formula is used to retrieve the data of the Date field from the dataset ds1, remove any duplicate elements from the array, and then return the result.

=

4. Choose Style > Legend and deselect Show Legend.

5. Choose Style > Label, tick Use Label, tick Value in Text, and select Outside in Position.

6. Choose Style > Series and set Series Gap to -100%.

Note:

In this example, since both Category and Series are bound to the same field Shipper's City, you need to set the series gap to -100% to better display the series graphics.

7. Choose Special Effect > Interaction, enable Background Detection in Monitor Refresh, and set Time Interval to 1 second. In this case, the chart will automatically be refreshed every 1 second.

Effect Display

1. PC

Save the report and click Pagination Preview. The section "Problem" shows the effect.

2. Mobile Terminal

The report can be previewed on both the DataAnalyst app and the HTML5 terminal. The following figure shows the effect.

Template Download

For details, you can click to download the template: Dynamic Carousel in a Line Chart.cpt.