I. Overview
1. Problem
How to compare the two series of line chart and mark the nodes that meet certain conditions?
For example, when the sales volume in Shanghai is higher than that in Beijing, highlight the mark, as shown in the following figure:
2. Solution
Use SQL to find the points that meet the conditions, and use the special effects > Conditional display to achieve the effect.
II. Example
1. Design template
1) Create a data query ds1, as the chart data source, the SQL statement is as follows:
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 to H15 and insert a line chart, as shown in the figure below:
3) Bind chart data, as shown in the figure below:
4) Create a data query ds2 to find out the date when the sales volume in Shanghai is higher than that in Beijing. The SQL statement is as follows:
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) Add a marker point in the chart special effects> conditional display .
When the series name is equal to Shanghai (string type) and the category name is in ds2.time (data column type), set the marker point to a circle and the color to be red, as shown in the following figure:
2. Effect preview
Save the report, click the pagination preview , the effect is as shown in the figure below:
III. Template download
The completed template can refer to: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc_EN\Chart\LineChart\LineChart_CompareSeries.cpt
Click to download the template:
Previous：Change The Attributes of Lines or Markers in a Line Chart
