Filtering and Displaying the Year-on-Year and Month-on-Month of "Natural Month"

Application Scenarios

When the component does not contain a date field, the user hopes to obtain the year-on-year (YoY) and month-on-month (MoM) of the natural month of the date by filtering the date through the filter component.

This documentation shows the YoY and MoM of natural month. If the user wants to eliminate the difference in the number of days in a month, for example, if they want to compare July 1–30 with June 1–30, they can refer to Filtering and Displaying the Year-on-Year and Month-on-Month of "A Specified Date" (Chart Has No Date Data).

For example, after entering "2014-03-01" in the date filter component, the component will automatically filter out the total contract price of each province in March 2014, and calculate the YoY and MoM of the total contract price in March. As shown in the following image:

Ways of Implementation

It is achieved by adding a date filter component without binding fields and calculating indicators.

This documentation shows the YoY and MoM calculation ways of natural month.

Adding a Component

Create an analysis topic, and add the built-in data "Provincial and Municipal Contract Data Statistics" in it, as shown in the following image:

Then create a component and use the data, as shown in the following image:

Creating a Parameter

Since the user wants to calculate the YoY that can be dynamically changed, they need to use a parameter to achieve the dynamics.

Add a time type parameter in the component editing interface, as shown in the following image:

The generated parameter is stored in the area to be analyzed, as shown in the following image:

Calculating the total contract price of the current month, last month, and the same period last year

Year-on-year = (Total contract price of the current month – Total contract price of the same period last year)/Total contract price of the same period last year * 100%

Month-on-month = (Total contract price of the current month – Total contract price of last month)/Total contract price of last month * 100%

Therefore, to get the YoY and MoM, the user needs three new fields: "total contract price of the current month", "total contract price of the same period last year", and "total contract price of last month". All three new fields need to be bound to the new parameter. Proceed as follows:

1. Calculating the "total contract price of the current month"

a. Copy the field "total contract price" and name it "total contract price of the current month".

b. Filter on the field of "total contract price of the current month". The result of the filtering is the data of the month selected by the date filter component.

2. Calculating the "total contract price of the same period last year"

a. Copy the field "total contract price" and name it "total contract price of the same period last year".

b. Filter on the field of "total contract price of the same period last year". The result of the filtering is the data of the same month last year as the date selected by the date filter component.

3. Calculating the "total contract price of last month"

a. Copy the field "total contract price" and name it "total contract price of last month".

b. Filter on the "total contract price of last month". The result of the filtering is the data of the previous month of the date selected by the date filter component.

Calculating YoY and MoM

The fields required for calculating the YoY and MoM have been calculated above, and then the YoY and MoM can be calculated.

1. Calculating the YoY field

Create a YoY calculation field. If the "Total contract price of the same period last year" has no value, the "YoY" field is null; otherwise, calculate the YoY value.

Enter the formula: if(ISNULL(sum_agg (Total contract price of the same period last year)), "", (sum_agg (Total contract price of the current month)-sum_agg (Total contract price of the same period last year))/sum_agg (Total contract price of the same period last year))

We divide the formula into two parts for users to understand:

2. Calculating the MoM field

Create a MoM calculation field. If the "total contract price of last month" has no value, the "MoM" field is null; otherwise, calculate the MoM value.

Enter the formula: if(ISNULL(sum_agg(Total contract price of last month)), "",(sum_agg(Total contract price of the current month)-sum_agg(Total contract price of last month))/sum_agg(Total contract price of last month))

We also divide the formula into two parts for users to understand:

Making a Grouping Table

After all the fields are calculated, it is very easy to make a chart component.

We will make a grouping table here. We drag the field into the analysis area, and change the numerical format of YoY and MoM to percentage. As shown in the following image: 

Note: Since no date is selected here, the total contract prices of the same province in the current month, last month and the same period last year are all equal, and both YoY and MoM are zero. When the date filter component selects a date, the YoY of the corresponding time will be displayed.

Making a Dashboard

1. Create a dashboard and add components to the dashboard, as shown in the following image:

2. Add the date filter component to the dashboard, as shown in the following image:

3. Bind parameters to the filter component, and the date filtered by the filter component is the dynamic date of the new parameter, as shown in the following image:

The user can change the numerical format of the YoY and MoM to percentage according to Numerical Format of Table.

Go to the dashboard and filter the date. Then the user can see the total contract price of the month of the selected date, the total contract price and MoM of the previous month, and the total contract price and YoY of the same period last year. For details, see section "Application Scenarios".