Author: Li Ling, Wang Xiaoyan Source: Agile Big Data
Source:Yixin Institute of Technology
Introduction: Year-on-year and month-on-month comparison are important indicators to measure the periodic growth rate changes of certain data in an enterprise. However, if we only look at the data, it is difficult to compare the growth rate. At this time, visualization tools are needed to help us. This time, Xiao Bian “fabricated” another set of data, using Moonbox to write SQL for year-on-year growth rate, and finally using Davinci to show it. Please see the text for details ~
Thank the almighty Wormhole elder sister Wang Xiaoyan classmate, Moon Box great god Wang Hao, for providing SQL technical support!
Ring comparison refers to the comparison between the current statistics and the previous statistics, such as the comparison between February 2018 and January 2018.
The year-on-year comparison refers to the comparison between the statistics of the current period and those of the same period in previous years, such as the comparison between January 2018 and January 2017.
Both year-on-year and month-on-month comparisons reflect the speed of change, but the emphasis is different: using month-on-month comparisons, we can see the short-term trend of the data, but this data may be affected by factors such as seasons. However, compared with the same period of last year, more emphasis is placed on reflecting the long-term general trend, so that we can avoid seasonal factors when analyzing the data.
Fig. 1 is a set of simple data fabricated at random by the editor. the header includes year, month, games, audience number, box office and advertising revenue. it is expressed in English for convenience when writing SQL later.
Let’s call this group of data “ABD Virtual Cinema Related Data” for the moment. Next, it is time to show the year-on-year comparison with Davinci!
Step 1: Add Data Sources
Click “+”in the upper right corner of the Source interface to add a data source (Figure 2) to the Source List and upload the CSV file to the specified database (Figure 3). The database used by the editor is MySQL.
After uploading the CSV file, we came to a particularly important step: writing SQL and calculating the year-on-year month-on-month growth rate.
The calculation of year-on-year comparison can be realized by using the window function in SQL. MySQL database only supports window function in version 8.x, however, the version of the database used by Xiaobian is 5.x, which is troublesome to upgrade, so Xiaobian uses Moonbox to calculate the year-on-year month-on-month growth rate here. The steps are as follows:
(1) mount that data source corresponding to the CSV file into the Moonbox compute engine.
(2) continue to add the data Source from the Moonbox in the source interface, and the editor will name it “growthSource”. the connection Url will write the Moonbox jdbc service address (e.g. JDBC: moonbox://localhost: 10010/growth source). pay attention to putting the Moonbox jdbc driver under the Davinci lib package.
Click Save to add a JDBC type data source.
Step 2: Add View
Click “+”in the upper right corner of the View interface to display the interface shown in fig. 6. [View is a very important concept in Davinci. All SQL logic needs to be created here. All data displayed on the chart is obtained through SQL here. Visual modeling and team data permission control are also carried out here. (from Davinci user manual)]
Click “Select a Source” in the upper left corner of Figure 6, select the newly added “growthSource” data source in the previous step, and then write SQL statements. The SQL statements for year-on-year and month-on-month growth rates are shown in Code Block 7 and Code Block 8 respectively.
select g1.year,g1.month, g1.box_office_mln, g1.last_year_month_box_office_mln, round((g1.box_office_mln - g1.last_year_month_box_office_mln)/g1.last_year_month_box_office_mln * 100.0, 2) as box_office_mln_year_growth, g1.advertising_revenue_mln, g1.last_year_month_advertising_revenue_mln, round((g1.advertising_revenue_mln - g1.last_year_month_advertising_revenue_mln)/g1.last_year_month_advertising_revenue_mln * 100.0, 2) as advertising_revenue_mln_year_growth, g1.screening_ths, g1.last_year_month_screening_ths, round((g1.screening_ths - g1.last_year_month_screening_ths)/g1.last_year_month_screening_ths * 100.0, 2) as screening_ths_year_growth, g1.audience_mln, g1.last_year_month_audience_mln, round((g1.audience_mln - g1.last_year_month_audience_mln)/g1.last_year_month_audience_mln * 100.0, 2) as audience_mln_year_growth from (select g.year, g.month, g.box_office_mln, lead(box_office_mln) over(partition by g.month order by g.year desc) as last_year_month_box_office_mln, g.advertising_revenue_mln, lead(advertising_revenue_mln) over(partition by g.month order by g.year desc) as last_year_month_advertising_revenue_mln, g.screening_ths, lead(screening_ths) over(partition by g.month order by g.year desc) as last_year_month_screening_ths, g.audience_mln, lead(audience_mln) over(partition by g.month order by g.year desc) as last_year_month_audience_mln from GrowthRate_SQL g order by g.month, g.year desc) g1;
Code block 7
select g1.year,g1.month , g1.box_office_mln, g1.last_month_box_office_mln, round((g1.box_office_mln - g1.last_month_box_office_mln)/g1.last_month_box_office_mln * 100.0, 2) as box_office_mln_month_growth, g1.advertising_revenue_mln, g1.last_month_advertising_revenue_mln, round((g1.advertising_revenue_mln - g1.last_month_advertising_revenue_mln)/g1.last_month_advertising_revenue_mln * 100.0, 2) as advertising_revenue_mln_month_growth, g1.screening_ths, g1.last_month_screening_ths, round((g1.screening_ths - g1.last_month_screening_ths)/g1.last_month_screening_ths * 100.0, 2) as screening_ths_month_growth, g1.audience_mln, g1.last_month_audience_mln, round((g1.audience_mln - g1.last_month_audience_mln)/g1.last_month_audience_mln * 100.0, 2) as audience_mln_month_growth from (select g.year, g.month, g.box_office_mln, lead(box_office_mln) over(partition by g.year order by g.month desc) as last_month_box_office_mln, g.advertising_revenue_mln, lead(advertising_revenue_mln) over(partition by g.year order by g.month desc) as last_month_advertising_revenue_mln, g.screening_ths, lead(screening_ths) over(partition by g.year order by g.month desc) as last_month_screening_ths, g.audience_mln, lead(audience_mln) over(partition by g.year order by g.month desc) as last_month_audience_mln from GrowthRate_SQL g order by g.year, g.month) g1;
Code block 8
Note: Two “View” have been added to the editorial here, namely, yearGrowth and monthGrowth. In addition, in order to facilitate calculation, when writing SQL, a new column of data is generated after the four columns of main data to represent the data of the same month last year or the same month last year.
Click “Execute” in the lower right corner to execute the SQL statement, and the data in yearGrowth and monthGrowth respectively change as follows:
Click “Model” to conduct visual modeling, that is, which items in the data are used as dimensions and which items are used as indicators. After the changes are completed, click “Save”. In these two View, the minor editing only uses year and month as dimensions, and the rest are indicators.
Step 3: Make Widget
The visualization component Widget is the most powerful and complex part of Davinci. The same data view can be used by multiple visual components and displayed with different graphics.
In the aspect of displaying year-on-year month-on-month data, we usually use bar charts or line charts, while both perspective drivers and chart drivers supported by Davinci have bar charts and line charts. What is the specific difference between them? Let’s feel it in an example ~
Note: Want to know about perspective drive and chart drive? Please refer to the Davinci user manual:
Click “+”in the upper right corner of Widget interface to select a View. After the selection is completed, the interface shown in fig. 11 appears.
Among them, the classified field corresponds to the dimension field set in View, and the numeric field corresponds to the indicator field. When the mouse hovers over the graphic icon, the system prompts the configuration requirements of the graphic to meet the conditions to generate the graphic.
For example, Xiaobian would like to know about the ring-on-ring growth trend of box office in the second half of 2017, which is represented by a histogram driven by perspective. In this requirement, simply drag and drop a few fields to make up the chart that the editor wants.
Select the View of “monthGrowth” and place your mouse on the histogram icon. We will find that “0 to multiple dimensions” and “1 to multiple indicators” are required to make this histogram. Since Xiaobian wants to see the month-on-month growth rate, we need to put the “month” field (click on the field drop-down menu to sort), and the indicator is the box office month-on-month growth rate field.
Next, we need to use the “screening” item. In the original data compiled in small numbers, the years include 2017 and 2018, and the months include 12 months. The requirement of the small editor here is “the second half of 2017”, so you need to drag the “year” and “month” fields into the filter column and configure the filter according to your own requirements. After these items are configured, the interface shown in Figure 12 appears:
If we think it is not clear enough to just look at graphics, we can also drag the box office month-on-month data into the label column, as shown in Figure 13.
Of course, you can also configure histogram color, title color and size, coordinate axis color, etc. according to your preferences (fig. 14).
Click “Save” in the upper right corner, and Xiaobian successfully created a Widget. Of course, this Widget can also be represented by a line chart (fig. 15), completely depending on personal needs ~
For another example, Xiaobian would like to try to use the line chart in the chart driver to show the year-on-year growth rate of advertising revenue for each month in 2018. This is the time to choose the “yearGrowth” View. Then put the mouse over the chart-driven line chart icon, and we will find that “1 dimension” and “1 to multiple indicators” are required to make this line chart.
Similarly, drag the “month” field into the dimension column and the “Year-on-Year Growth Rate of Advertising Revenue” field into the indicator column.
Hmmmmm, that’s it. (Figure 16)
Is it too simple?
But did you find a problem: there is no “label column” in this data configuration column, but there are still numbers in the line chart.
Originally, the label here is set in the style configuration.
Check “Show Label” in the style configuration, and the number will appear in the line chart (Figure 17).
Wait a minute! The editor seems to have discovered that line charts can change appearance. Click once, found that indeed smooth line chart is more in line with the intention of small editor. So the final line chart of “year-on-year growth rate of advertising revenue for each month in 2018” was completed! (Figure 18)
Finally, for example, Xiaobian would like to see whether the increase or decrease of the number of games in 2018 is related to the amount of advertising revenue in each month. Histogram and line chart in perspective drive and chart drive can both be expressed. For the time being, the small editor chooses line chart in perspective drive.
Similarly, drag the required fields into the dimension column and indicator column-“month” into the dimension column, “advertising revenue” and “sessions” into the indicator column, drag “year” into the filter column, and select 2018. Done! (Figure 19)
It is worth mentioning that here we can also change the graphs in the indicator column according to our preferences. (Figure 20)
As a result, Xiaobian completed many Widget productions by dragging and dragging.
Finally, we came to the display interface. In the Display interface, there are Dashboard and display respectively. Dashboard has many more advanced functions, such as linkage configuration and drilling settings.
However, the superficial editor was temporarily attracted by the Display Display, and all interests were hung on the Display. therefore, the editor in this article only introduces the display display, and then introduces the Dashboard function in detail.
Now let’s introduce Davinci’s Display display function.
In fact, there is no introduction, just take the picture above.
Figure 21 Simple Wind
Figure 22 Cartoon Style
Figure 23 Scientific and Technological Wind
As shown in the figure, in the Display, we can change the background color or upload the background picture. After setting the background, click on the “Widgets” icon in the upper left corner, upload the chart you want to show, arrange it into the shape you like, choose your style, and make large screens one by one.
The above is the process of small editors using Davinci to show the month-on-month comparison. Since the data are compiled by ourselves, it is inevitable that there will be discrepancies. Please understand.
In addition, Davinci has been growing continuously. In the future, we still support the function of directly calculating the year-on-year month-on-month growth rate in the Davinci Widget. please be patient and continue your support.
Because your support is the driving force for our continuous progress!
A large part of the Display design in this article is based on the large screen made by Davinci users. of course, there is only a little copy here. there is still a long way to go to learn the essence of the Display made by da vinci. I also ask all the great gods to give their advice and put on their exquisite Display made by Davinci or form a tutorial essay, so that everyone can learn more about how to make exquisite large screens ~