Shulou(Shulou.com)06/03 Report--

Introduction: year-on-year and month-on-month are important indicators to measure the periodic growth rate of a company's data, but blindly looking at the data, it is difficult for us to compare the growth rate, so we need visualization tools to help us at this time. This time, the editor "fabricated" another set of data, using Moonbox to write SQL to calculate the year-on-year growth rate, and finally using Davinci to show it. For details, please see the text.

Davinci introduction: Davinci is a DVAAS (Data Visualization as a Service) platform solution. Davinci is dedicated to providing one-stop data visualization solutions for business people / data engineers / data analysts / data scientists. It can be used independently as a public / private cloud or integrated into a three-party system as a visual plug-in. Users can serve a variety of data visualization applications by simply configuring on the visual UI, and support visualization functions such as advanced interaction / industry analysis / pattern exploration / social intelligence.

Special thanks

Thanks to the omnipotent Wormhole first sister Wang Xiaoyan & Moonbox god Wang Hao for providing SQL technical support!

1. Introduction

Month-on-month comparison refers to the comparison of the current statistics with the previous statistics, such as the February 2018 data and the January 2018 data.

Year-on-year refers to the comparison of the statistics of this issue with those of the same period in previous years, such as January 2018 and January 2017.

Both year-on-year and month-on-month reflect the speed of change, but the focus is different: using month-on-month comparison, we can see the short-term trend of the data, but this data may be affected by factors such as seasons, while year-on-year is more focused on reflecting long-term general trends. in this way, we analyze the data to avoid seasonal factors.

Figure 1 is a simple set of data randomly made up by the editor, including year, month, number of shows, audience size, box office and advertising revenue. It is expressed in English to make it easier to write SQL later.

(figure 1)

Let's call this group of data "ABD virtual cinema related data". Next, it's time to show year-on-year comparison with Davinci.

2. ProcessStep 1: add data sources

Click "+" in the upper right corner of the Source interface, add data sources in Source List (figure 2), and upload CSV files to the specified database (figure 3). The database used by the editor is MySQL.

(figure 2)

(figure 3)

After uploading the CSV file, we came to a particularly important step: to write SQL and figure out the year-on-year growth rate.

The year-on-year ratio can be calculated by using the window window function in SQL. The window function is only supported in MySQL database version 8.x, but the database version used by the editor is 5.x, so it is troublesome to upgrade, so here the editor uses Moonbox to calculate the year-on-year growth rate. The steps are as follows:

(1) Mount the data source corresponding to the CSV file into the Moonbox computing engine.

(figure 4)

(2) continue to add the data source from Moonbox in the Source interface, and the editor names it "growthSource". Connect to Url and write the address of the Moonbox jdbc service (for example: jdbc:moonbox://localhost:10010/growthSource). Pay attention to putting the Moonbox jdbc driver under the Davinci lib package.

(figure 5)

Click Save, and a new data source of type JDBC is added.

Step 2: add View

Click "+" in the upper right corner of the View interface to show the figure 6 interface. [View is a very important concept in Davinci. All SQL logic needs to be created here, and all the data shown on the chart is obtained through SQL here. Visual modeling and team data permission control are also carried out here. (quoted from Davinci user's manual)]

(figure 6)

Click "Select a Source" in the upper left corner of figure 6, select the "growthSource" data source added in the previous step, and then write the SQL statement, which is shown in code block 7 and code block 8, respectively, for year-on-year and month-on-month growth rates.

Select g1.yearjournal 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) asbox_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) asscreening_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) aslast_year_month_box_office_mln, g.advertising_revenue_mln Lead (advertising_revenue_mln) over (partition by g.month order by g.year desc) aslast_year_month_ advertising_revenue_mln, g.screening_ths, lead (screening_ths) over (partition by g.month order by g.year desc) aslast_year_month_screening_ths, g.audience_mln, lead (audience_mln) over (partition by g.month order by g.year desc) aslast_year_month_ audience_mln from GrowthRate_SQL g order by g.month, g.year desc) G1

(code Block 7)

Select g1.yearjournal 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) asadvertising_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) asaudience_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 byg.month desc) as last_month_advertising_revenue_mln, g.screening_ths, lead (screening_ths) over (partition by g.year order byg.month desc) as last_month_screening_ths, g.audience_mln, lead (audience_mln) over (partition by g.year order byg.month desc) as last_month_audience_mln from GrowthRate_SQL g order by g.year, g.month) G1

(code Block 8)

Note: here the editor has added two "View", 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 master data, which is used to represent the data of the same month last year or last month of the same year.

Click "Execute" in the lower right corner to execute the SQL statement. The data in yearGrowth and monthGrowth will be changed as follows:

(figure 9)

(figure 10)

Click "Model" for visual modeling, that is, which items in the data are used as dimensions and which are used as indicators. When the changes are complete, click Save. In these two View, the editor only uses year and month as dimensions, and the rest as indicators.

Step 3: making 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 presented in different graphics.

In terms of showing year-on-year data, we usually use bar charts or line charts, while in the perspective drivers and chart drivers supported by Davinci, there are bar charts and line charts. What's the difference between them? Let's feel it in an example.

Note: want to know about perspective driver and chart driver? Please refer to the Davinci user manual:

Https://edp963.github.io/davinci/widget_guide.html

Click "+" in the upper right corner of the Widget interface and select a View. After the selection, the interface shown in figure 11 appears.

(figure 11)

Among them, the type field corresponds to the dimension field set in the View, and the numerical field corresponds to the indicator field. When the mouse hovers over the graphic icon, the system prompts the configuration requirements of the graphic and meets the conditions to generate the graphic.

For example, the editor wants to know the month-on-month growth rate trend of the box office in the second half of 2017, represented by a bar chart driven by perspective. In this requirement, you can simply drag and drop a few fields to create the chart that the editor wants.

Select the "monthGrowth" View, mouse over the bar chart icon, we will find that to make this bar chart will require "0 to more dimensions" and "1 to more indicators". Since the editor wants to see the month-on-month growth rate, we need to put the "month" field here (click 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 editor's original data, the year includes 2017 and 2018, and there are 12 months in the month. The editor's requirement here is "the second half of 2017", so you need to drag both "year" and "month" fields to the filter column and configure the filter according to your own needs. After these items are configured, the interface shown in figure 12 appears:

(figure 12)

If we think that just looking at the graphics is not clear enough, we can also drag the box office quarter-on-quarter data into the label bar, as shown in figure 13:

(figure 13)

Of course, you can also configure the histogram color, title color and size, and axis color according to your preferences (figure 14).

(figure 14)

Click "Save" in the upper right corner, and the editor has successfully made a Widget. Of course, this Widget can also be represented by a line chart (figure 15), depending on individual needs.

(figure 15)

For example, the editor wants to try the chart-driven line chart to show the year-on-year growth rate of advertising revenue in 2018. At this time, we should choose the View of "yearGrowth". Then mouse over the chart-driven line chart icon, we will find that to do this line chart requires "1 dimension" and "1 to more indicators".

Similarly, drag the "month" field into the dimension bar, and the "year-on-year growth rate of advertising revenue" field into the indicator bar.

Hmmmmm, it's done. (figure 16)

(figure 16)

Is it too simple?

But have you found a problem: there is no "label bar" in this data configuration bar, but there are still numbers in the line chart.

It turns out that the label here is set in the style configuration.

Check the "display label" in the style configuration and the number appears in the line chart (figure 17).

(figure 17)

Wait a minute! The editor seems to have found that a line chart can change the face. I clicked on it and found that the smooth line chart was more in line with the editor's mind. So the final "year-on-year growth rate of advertising revenue in 2018" line chart has been completed! (figure 18)

(figure 18)

Finally, for example, the editor wants to see if there is any relationship between the increase or decrease in the number of monthly shows in 2018 and the monthly advertising revenue. The bar chart and line chart in perspective driver and chart driver can be represented, and the editor will choose the line chart in perspective driver for the time being.

Similarly, drag the required fields into the dimension bar and indicator bar-"month" into the dimension bar, "advertising revenue" and "times" into the indicator bar, and drag "year" into the filter bar to select 2018. Done! (figure 19)

(figure 19)

It is worth mentioning that here we can also change the graphics in the index bar according to our preferences. (figure 20)

(figure 20)

As a result, the editor completed a lot of Widget production by dragging and dragging.

III. Display

Finally, we came to the display interface. In the display interface, there are two display modes: Dashboard and Display. There are many more advanced features in Dashboard, such as linkage configuration and drillthrough settings.

However, the superficial editor is temporarily attracted by the Display presentation, and all interests are on Display, so in this article, the editor only introduces the Display presentation, and the Dashboard function will be described in detail later.

Let's introduce the Display presentation function of Davinci.

In fact, there is no introduction, just go to the picture!

(figure 21 simple style)

(figure 22 cartoon style)

(figure 23)

As shown in the figure, in the Display presentation, we can change the background color or upload the background image ourselves. After setting the background, click the "Widgets" icon in the upper left corner, upload the chart you want to show, arrange it into your favorite shape, and choose your style.

The above is the process of using Davinci to show the year-on-year comparison. As the data are compiled by ourselves, it is inevitable that there will be some inconsistencies. Please understand.

In addition, Davinci has been growing. In the future, we still support the function of directly calculating the year-on-year growth rate in Davinci Widget, and please be patient and continue to support it.

Because your support is the driving force for us to move forward!

A large part of the Display design of this article draws lessons from the big screen made by Davinci users, of course, there is only a little copy here, and there is still a long way to go to learn the essence of Display made by God. Please do not hesitate to comment on the exquisite Display made by yourself with Davinci or form a tutorial essay, so that you can learn more about the method of making exquisite big screen.

Author: Li Ling and Wang Xiaoyan

Source: Yixin Institute of Technology

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.