I.Overview
1.Problem Description
When previewing the line chart, sometimes it is not necessary to display all the content, but as the time dimension increases, they are displayed in sequence.
As shown below:
Effect 2：
2.Realization Ideas
Set the time parameter in the SQL query statement to filter the data, the value of the time parameter will change with time, and then refresh the chart regularly.
II.Example
1. Option 1: Dynamic Changes of Classification Value
1)Report design
Create a new decision report, create a data set ds1, the database query statement is as follows:
SELECT
city,
strftime("%Y-%m",sale.time ) SALETIME,
sum(sales) sales
FROM sale
where strftime('%Y-%m',sale.time)<='${left(monthdelta("2017-01-01",mod((datetonumber(now())-datetonumber(a))/1000,12)),7)}'
and strftime('%Y',sale.time)='2017'
and city in ('Beijing','Shanghai','Guangzhou')
group by city,strftime("%Y-%m",sale.time)
Click Template>Template Parameters on the menu bar, and create a new template parameter a with the default value =now.
The layout of the body component of the decision report is changed to an absolute layout. Drag a line chart into the main body of the decision report design, and bind data information as shown in the figure below.
Select the Label for the line chart style, check the Use label, and the text check the Value.
Add regular refresh, select interactive attributes for special effects of the line chart, enable background detection, and the time interval is 1 second.
2) Effect preview
PC terminal
Save the report, click Data Analysis Preview, the effect of the line graph is shown in the figure below:
Mobile terminal
2. Option 2: Fixed classification value
1)Report design
The data set ds1 is modified as follows:
select a.city,a.SALETIME,b.sales from (
SELECT
distinct city,strftime("%Y-%m",sale.time) SALETIME
FROM sale
where strftime('%Y',sale.time)='2017'
and city in ('Beijing','Shanghai','Chengdu')
) a
left join (
SELECT
city,
strftime("%Y-%m",sale.time) SALETIME,
sum(sales) sales
FROM sale
where strftime('%Y-%m',sale.time)<='${left(monthdelta("2017-01-01",mod((datetonumber(now())-datetonumber(a))/1000,12)),7)}'
and strftime('%Y',sale.time)='2017'
and city in ('Beijing','Shanghai','Chengdu')
group by city,strftime("%Y-%m",sale.time)
) b on a.city=b.city and a.SALETIME=b.SALETIME
The other steps can be set according to option1.
2 )Effect preview
PC terminal
Save the report, click Data Analysis Preview, the effect of the line graph is shown in the figure below:
Mobile terminal
III.Template Download
Click to download the template:
Dynamic_change_of_classification_value.frm
The_classification_value_is_fixed.frm