Series Comparison and Mark Point Setting of Line Charts

Problem

How to compare two series in a line chart and mark the points satisfying the conditions?

For example, the mark point is highlighted if the sales of Shanghai it represents is higher than those of Beijing, as shown in the following figure.

Implementation Method

Use the SQL statement to find the points that satisfy the conditions, and choose Special Effect > Condition Display to realize the effect.

Template Design

1. Create a database query ds1 as the data source of the chart with the following SQL statement: 

SELECT time,city,sum(sales) as sales

FROM sale

where city in ('Shanghai','Beijing')

and time<='2017-01-07'

group by time,city

2. Merge cells A1~D18 and insert a line chart as shown in the following figure.

3. Bind the data to the chart, as shown in the following figure.

4. Create a database query ds2 to filter the dates on which the sales of Shanghai are higher than those of Beijing, with the following SQL statement:

SELECT time,city,sum(sales) as sales

FROM sale a

where city = 'Shanghai'

and time<='2017-01-07' 

group by time,city

having sum(sales)>(select sum(sales) from sale

where city = 'Beijing' 

and time= a.time)

5. Choose Special Effect > Condition Display, and add Condition Attribute1 for Mark Point.

If the series name is  Shanghai (string type) and the category name is in the time column in ds2, and set  Type of Mark Point to round and Fill Color to red, as shown in the following figure.

Effect Display

PC

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

Mobile Terminal

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

For details, you can download the template Series Comparison and Mark Point Setting of Line Charts.cpt