Use date range filtering to display the YoY and chain rate

1. Overview

1.1 Background

Sometimes we don't want to calculate the YoY and chain rate of the entire calendar month.

For example, there is a National Day holiday in October. If you want to know the chain rate of the total contract price in October, it is not feasible to directly compare the data in September and October. The data of "8-31" must be used compared with the "8-31" of September to better show the contract signing situation.

For example, this month has just arrived on the 15th, he wants to know the chain rate of the total contract price on the 1-15th this month with the 1-15th of the last month, and the YoY rate on the 1-15th of the same period last year. How to calculate it?

1.2 Expected effect

Filter the date range from 2013-03-01 to 2013-03-15, and the total contract price of the current period will be automatically filtered in the grouping table. And calculate the YoY and chain rate for this period, as shown in the following figure:

1.3 Implementation ideas

It is realized by adding date filter components and calculation indicators that are not bound to fields.

2. Operation steps

Create a new dashboard and enter the dashboard edit page.

2.1 Add components

2.1.1 Add date range filter component

First, create a new date range filter component to prepare for the date range filtering.

1) Drag a date filter component into an empty dashboard, as shown in the figure below:

2) Click "OK" directly on the filter component edit page, as shown in the figure below:

2.1.2 Add component

Use FineBI's demo data "Provincial and municipal contract data" to add a new component. As shown below:

2.2 Calculate the total contract price for each period

The YoY rate=(Total contract price in current period-Total contract price in the same period last year)/Total contract price in the same period last year*100%

The chain rate = (Total contract price in current period-Total contract price in the same period last month)/Total contract price in the same period last month*100%

Therefore, to calculate the YoY and chain rate, we need three new fields: "Total contract price in current period", "Total contract price in the same period last year", and "Total contract price in the same period last month". These three new fields all need to be bound to our newly created date range filter component in Section 2.1. Proceed as follows:

2.2.1 Calculate the "Total contract price in current period"

1) Copy the "Total contract price" field and name it as the "Total contract price in current period".

2) Perform detailed filtering on the "Total contract price in current period" field. The effect of filtering is to filter the total price of the contract to obtain the data of the date interval selected by the filtering component.

2.2.2 Calculate the "Total contract price in the same period last year"

1) Copy the "Total contract price" field and name it "Total contract price in the same period last year";

2) Perform detailed filtering on the field of "Total contract price in the same period last year". The effect of filtering is: to filter the total contract price to obtain the same time period data one year before the date interval selected by the filter component.

2.2.3 Calculate the "Total contract price in the same period last month"

1) Copy the "Total Contract Price" field and name it "Total contract price in the same period last month";

2) Perform detailed filtering on the "Total contract price in the same period last month" field. The effect of filtering is to filter the total contract price to obtain the same time period data one month before the date interval selected by the filter component.

2.3 Calculation of the YoY and chain rate

2.3.1 Calculate "The YoY rate" field

Add a "The YoY rate" field. If the "Total contract price in the same period last year" has no value, then "The YoY rate" field is blank, otherwise "The YoY rate" value is calculated.

Here we split the formula into two parts for users to understand:

2.3.2 Calculate "The chain rate" field

Add a chain rate calculation field. If the "Total contract price in the same period last month" has no value, then "The chain rate" field is blank, otherwise the chain rate is calculated.

Here, the formula is also split into two parts for users to understand:

2.4 Making a group table

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

Here we make a group table, drag the fields into the analysis area, and change the numerical format of "The YoY rate", "The chain rate" to percentage. As shown below:

2.5 Effect view

Go to the dashboard and filter the date. As shown below: