Filtering and Displaying the Same/Previous Period Ratio of a Self-Defined Date

Application Scenario

Sometimes you don't want to compare the same/previous period ratio of the entire calendar month.

For example, in October, there is a National Day holiday in China. If you want to know the previous period ratio of the total contract price in October, comparing the data of September and October directly may not be feasible. To better demonstrate the contract signing situation, you are advised to use the data from October 8th to October 30th to compare with the data from September 8th to September 30th. Take another example, when it is the 15th day of a month, you want to know the previous period ratio of the total contract price for the period from the first day to the 15th day in this month to that for the same period last month, as well as the same period ratio for the same period last year.

Expected effect: Perform filtering to get the data of the total contract price from 2013-03-15 to 2013-03-31 in the group table, and calculate the  same period and previous period ratios for this time period, as shown in the following figure.

Implementation Method

You can achieve the effect by adding a date filter component without binding fields, and calculation indicators.

1. Add time parameters to the component.

2. Use parameters to get the data of the total contract price in current time period, last month, and last year. As you can select a time period dynamically, you need to set the filtering condition to Parameter of XXX Same Timestep.

3. Calculate the  same period and previous period ratios and create a component and a dashboard.

4. Add a Date Interval filter component to the dashboard. Only tick Bind Parameter for the filter component.

5. Then, you can obtain the dynamic  same period and previous period ratio data of the total contract price by selecting the date interval.

Component Creation

Create an analysis subject, and add the built-in data Provincial and municipal contract data statistics to it, as shown in the following figure.

Then create a component and use the data, as shown in the following figure.

Parameter Adding

As you want to calculate the same period and previous period ratios of a self-defined date that can be dynamically changed, you need to use the parameter function.

1. Click the icon , select Add Parameter from the drop-down list, and set Parameter Type to Time, as shown in the following figure.

2. The generated parameter is stored in the to-be-analyzed area, as shown in the following figure. 

Calculation of the Total Contract Price in Each Time Period

The same period ratio=(Total contract price in current time period – Total contract price in the same time period last year)/Total contract price in the same time period last year * 100%

The previous period ratio=(Total contract price in current time period – Total contract price in the same time period last month)/Total contract price in the same time period last month * 100%

According to the above formulas, you may see that the following three new fields are needed if you want to get the same period and previous period ratios by month: Total contract price in current time period, Total contract price in the same time period last year, and Total contract price in the same time period last month. All three new fields need to be bound to the created parameter.

Calculation of the Total Contract Price in Current Time Period

1. Copy the Total contract price field and rename it Total contract price in current time period.

2. Perform detailed filtering for the Total contract price in current time period field. The step is to get the data of the date selected by the date interval filter component.

Calculation of the Total Contract Price in the Same Time Period Last Year

1. Copy the Total contract price field and rename it Total contract price in the same time period last year.

2. Perform detailed filtering for the Total contract price in the same time period last year field. The step is to get the data of the same time period last year as the date selected by the date interval filter component.

Calculation of the Total Contract Price in the Same Time Period Last Month

1. Copy the Total contract price field and rename it Total contract price in the same time period last month.

2. Perform detailed filtering for the Total contract price in the same time period last month field. The step is to get the data of the same time period last month before the selected date interval by the filter component.

Ratio Calculation of the Same/Previous Period

Ratio Calculation Indicator of the Same Period

Add a YoY Ratio calculation indicator field. If the Total contract price in the same time period last year field has no value, the YoY Ratio field is null; otherwise, the same period ratio value is calculated.

The formula is divided into two parts for you to better understand it.

Ratio Calculation Indicator of the Previous Period

Add a MoM Ratio calculation indicator field. If the Total contract price in the same time period last month field has no value, the MoM Ratio field is null; otherwise, the previous period ratio value is calculated.

The formula is also divided into two parts for you to better understand it.

Group Table Creation

After all the fields are calculated, you can easily create a chart component.

Create a group table, drag the fields into the analysis area, and set Value Format of the YoY Ratio and MoM Ratio fields to Percentage, as shown in the following figure.

Dashboard Creation

1. Create a dashboard and add the component to the dashboard.

2. Add a date interval filter component to the dashboard.

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

Note:

Do not drag any fields into the filter component, and do not tick Component in Control Range. 

You can change the value format of the YoY Ratio and the MoM Ratio fields to Percentage.

Effect Display

Go to the dashboard and filter the date. Then you can see the total contract price in current time period of the selected date, the total contract price in the same time period last month and the ratio of the previous period, and the total contract price in the same time period last year and the ratio of the same period.