How to configure year-on-year and month-on-month metrics - Quick BI

This topic describes how to configure the comparison feature for a measure.

Prerequisites

A dashboard is created. For more information, see Create a dashboard.
A dataset is selected and chart fields are configured. For more information, see Configure a chart.
A filter bar is added. For more information, see Create a filter bar.

Note

You must select a date field for the Compare Date field and the corresponding date filter condition. You cannot select a date placeholder.

Background information

Periodicity-valued comparison: shows the change compared with the previous statistical period, such as the month-on-month comparison between January 2021 and December 2020.
Interval-valued comparison: shows the change compared with the same period of the previous year, such as the year-on-year comparison between January 2021 and January 2020.

Quick BI supports the following comparison types for date fields based on the time granularity of these fields:

Date Field Category	Example	Same comparison description
Year	shipping_date(year)	Compare (Year to Year): compares data between the current year and the previous year, such as 2021 and 2020.
Quarter	shipping_date(quarter)	Compare (Quarter to Quarter): compares data between the current quarter and the previous quarter, such as the first quarter of 2021 and the fourth quarter of 2020. Compare (of Last Year): compares data between the current quarter and the same quarter of the previous year, such as the first quarter of 2021 and the first quarter of 2020.
Month	shipping_date(month)	Compare (Month to Month): compares data between the current month and the previous month, such as January 2021 and December 2020. Compare (of Last Year): compares data between the current month and the same month of the previous year, such as January 2021 and January 2020.
Week	shipping_date(week)	Compare (Week to Week): compares data between the current week and the previous week, such as the second week and the first week in 2021. Compare (of Last Year): compares data between the current week and the same week of the previous year, such as the first week in 2021 and the first week in 2020.
Day	shipping_date(day)	Compare (Day to Day): compares data between the current day and the previous day, such as January 13, 2021 and January 12, 2021. Compare (of Last Week): compares data between the current day and the same day of the previous week, such as January 13, 2021 and January 6, 2021. Compare (of Last Month): compares data between the current day and the same day of the previous month, such as January 13, 2021 and December 13, 2020. Compare (of Last Year): compares data between the current day and the same day of the previous year, such as January 13, 2021 and January 13, 2020.
Hour, minute, or second, or YYYY/MM/DD HH:mm:ss	report_date(hour), report_date(minute), report_date(second), report_date(ymdhms)	You can configure a custom comparison.

Limits

If Advanced Calculation is configured for a measure, Comparison is unavailable.
If Comparison is configured for a measure, Advanced Calculation is unavailable.

Examples of configuring the comparison feature

One date field in dimensions
- One date field in dimensions (day)
- One date field in dimensions (month)
Multiple date fields in dimensions
No date field in dimensions

One date field in dimensions (day)

If dimensions contain only one date field, set the granularity for the comparison feature to the granularity of this field.

The following example shows how to configure the comparison feature for a measure. A cross table is used in this example.

On the Field tab of the dashboard edit page, add fields and configure a comparison for the fields.
1. Add fields.
  - In the Dimension list, find shipping_date(day) and double-click or drag this dimension to the Rows section.
  - In the measure list, find order_amt and double-click or drag this measure to the Columns section.
    Note
    To view the order amount and Compare (Day to Day), Compare (Week to Week), Compare (Month to Month), and Compare (of Last Year) of the order amount, you can add five order_amt fields to the Columns section.
2. Move the pointer over an order_amt field in the Columns section, click the icon next to the field, and then choose Comparison > Compare (Day to Day).
  Refer to this operation to modify the same-period comparison of the order amount of the measure field to daily comparison, weekly comparison, monthly comparison, and annual comparison.
  Note
  In the comparison settings, the default value of Data Settings is Percent Difference From. You can choose Comparison > Custom and change the value of Data Settings in the Comparison Settings dialog box. For more information about the parameters, see Comparison Implementation.
3. Click Update.
(Optional)
Configure filters.
If you want to filter data that is generated in a specific time interval, perform this step. The following example describes how to add a query control and configure the order date (day).
1. Add a query control.
2. Select Add regular Control.
3. Set query conditions.
  Note
  The date field that you specified in the filter bar must be the same as the date field that you specified for the comparison feature.
In the filter bar of the dashboard edit page, click Inquire.
You can also modify the date range.

One date field in dimensions (month)

If dimensions contain only one date field, set the granularity for the comparison feature to the granularity of this field.

The following example shows how to configure the comparison feature for a measure. A cross table is used in this example.

On the Field tab of the dashboard edit page, add fields and configure a comparison for the fields.
1. Add fields.
  - In the Dimension list, find shipping_date(month) and double-click or drag this dimension to the Rows section.
  - In the measure list, find order_amt and double-click or drag this measure to the Columns section.
    Note
    To view the order amount and Compare (Month to Month) and Compare (of Last Year) of the order amount, you can add four order_amt fields to the Columns section.
2. Move the pointer over the order_amt field in the Columns section, click the icon next to the field, and then choose Comparison > Compare (Month to Month).
  Refer to this operation to modify the month-on-month comparison and year-on-year comparison of the order amount in the measurement field.
  Note
  In the comparison settings, the default value of Data Settings is Percent Difference From. You can choose Comparison > Custom and change the value of Data Settings in the Comparison Settings dialog box. For more information about the parameters, see Comparison Implementation.
3. Click Update.
(Optional)
Configure filters.
If you want to filter data that is generated in a specific time interval, perform this step. The following example describes how to add a query control and configure the order date (month).
1. Add a query control.
2. Select Add regular Control.
3. Set query conditions.
  Note
  The date field that you specified in the filter bar must be the same as the date field that you specified for the comparison feature.
In the filter bar of the dashboard edit page, specify a time range and click Inquire.
In this example, the data from 2009-01 to 2009-12 is filtered out. The system automatically calculates the month-on-month and year-on-year data.

Multiple date fields in dimensions

If dimensions contain multiple date fields, the date field of the smallest granularity is used for the comparison feature.

The following example shows how to configure the comparison feature for a measure. A cross table is used in this example.

On the Field tab of the dashboard edit page, add fields.
- In the Dimension list, find shipping_date(day) and shipping_date(month) and double-click or drag the dimensions in sequence to the Rows section.
- In the measure list, find order_amt and double-click or drag this measure to the Columns section.
Move the pointer over a field in the Columns section, click the icon next to the field, and then choose Comparison > Custom.

In the Comparison Settings dialog box, configure the parameters that are described in the following table.

Parameter	Parameter	Description
Comparison Date	shipping_date(day)	If dimensions contain multiple date fields, the field of the smallest granularity is used and cannot be modified. In this example, the value is shipping_date(day).
Comparison Type	Compare (Day to Day) Compare (of Last Week) Compare (of Last Month) Compare (of Last Year)	The valid values of Comparison Type vary based on the value of Comparison Date. In this example, this parameter is set to Compare (Day to Day).
Data Settings	Specific Value Difference From Percent Difference From Proportion	If you select Percent Difference From, you can select a formula to calculate data.
Same Period Comparison (takes effect only when the filter is associated with the daily granularity)	Selected or cleared	If you configure a filter, the filter also takes effect when you perform the same period comparison. For example, if you specify the date January 2, 2022 in a filter, this date is also included when you perform Compare (Year to Year) for the comparison of year 2021. If you select Month-to-Month Comparison and the number of days in a month is not aligned (for example, February has 28 days and January has 31 days), compare the data from February 1, 2023 to February 28, 2023 to January 1, 2023 to January 28, 2023. If you do not select Same-Period Comparison, compare the data from February 1, 2023 to February 28, 2023 to January 1, 2023 to January 31, 2023.
Formula	(Value of Current Period/Value of Previous Period - 1) × 100% (default value) 1 - (Value of Current Period/Value of Previous Period) × 100% Displayed As -	The formula takes effect only when Data Settings is set to Percent Difference From and Value of Previous Period is less than 0.

Click OK.
Perform the steps that are shown in the following figure to add and configure a filter bar.
1. Add a query control.
2. Select Add regular Control.
3. Set query conditions.
In the filter bar of the dashboard edit page, specify a time range and click Inquire.

No date field in dimensions (day)

If dimensions do not contain date fields and you want to configure the comparison feature for the chart, configure a date field for the filter bar that is associated with the chart. Then, in the Comparison Settings dialog box, set Comparison Date to a field of the same granularity as the date field in the filter bar.

The following example shows how to configure the comparison feature for a measure. A cross table is used in this example.

On the Field tab of the dashboard edit page, add fields and configure a comparison for the fields.
1. Add fields.
  - In the Dimension list, find area and double-click or drag this dimension to the Rows section.
  - In the measure list, find order_amt and double-click or drag this measure to the Columns section.
2. Move the pointer over the order_amt field in the Columns section, click the icon next to the field, and then choose Comparison > Custom.
3. In the Comparison Settings dialog box, specify Comparison Date, Comparison Type, and Data Settings.
  In this example, Comparison Date is set to shipping_date(day), Comparison Type is set to Compare (Day to Day), and Data Settings is set to Percent Difference From.
  Note
  In the comparison settings, the default value of Data Settings is Percent Difference From. For more information about the parameters, see Comparison Implementation.
4. Click OK.
Configure filters.
The following example describes how to add a query control and configure the order date (day).
1. Add a query control.
2. Select Add regular Control.
3. Set query conditions.
  Note
  The date field that you specified in the filter bar must be the same as the date field that you specified for the comparison feature.
In the filter bar of the dashboard edit page, click Inquire.
You can also modify the date range.

No date field in dimensions (year)

The following example shows how to configure the comparison feature for a measure. A cross table is used in this example.

On the Field tab of the dashboard edit page, add fields and configure a comparison for the fields.
1. Add fields.
  - In the Dimension list, find area and double-click or drag this dimension to the Rows section.
  - In the Measures list, double-click order_amt. You can also drag this measure to the Columns field.
2. Move the pointer over the order_amt field in the Columns section, click the icon next to the field, and then choose Comparison > Custom.
3. In the Comparison Settings dialog box, specify Comparison Date, Comparison Type, and Data Settings.
  In this example, Comparison Date is set to shipping_date(year), Comparison Type is set to Compare (Year to Year), and Data Settings is set to Difference From.
  Note
  You can set Data Settings to Specific Value, Difference From, Percent Difference From, or Proportion. For more information about the parameters, see Comparison Implementation.
4. Click OK.
Configure filters.
The following example describes how to add a query control and configure the order date (year).
1. Add a query control.
2. Select Add regular Control.
3. Set query conditions.
  Note
  The date field that you specified in the filter bar must be the same as the date field that you specified for the comparison feature.
In the filter bar of the dashboard edit page, specify a time range and click Inquire.
You can also modify the date range.

No date field in dimensions (dynamic comparison)

If dimensions do not contain date fields, you must select a date field in the Comparison Settings dialog box to configure the parameters of the comparison feature.

The comparison settings in this scenario are similar to the comparison settings in the scenario where multiple date fields exist in dimensions. If no date fields exist in dimensions, the Comparison Type parameter can also be set to Dynamic Comparison. The following example shows how to configure dynamic comparison. A cross table is used in this example.

On the Field tab of the dashboard edit page, add fields and configure a comparison for the fields.
1. Add fields.
  - In the Dimension list, find product_box and double-click or drag this dimension to the Rows section.
  - In the Measures list, double-click Profit. You can also drag this measure to the Columns field.
2. Move the pointer over the profit_amt field in the Columns section, click the icon next to the field, and then choose Comparison > Custom.
3. In the Comparison Settings dialog box, specify Comparison Date, Comparison Type, and Data Settings.
  In this example, Comparison Date is set to shipping_date(day), Comparison Type is set to Dynamic Comparison, and Data Settings is set to Percent Difference From.
  Note
  You can set Data Settings to Specific Value, Difference From, Percent Difference From, or Proportion. For more information about the parameters, see Comparison Implementation.
4. Click OK.
Configure filters.
The following example describes how to add a query control and configure the order date (day).
1. Add a query control.
2. Select Add regular Control.
3. Set query conditions.
  Note
  The date field that you specified in the filter bar must be the same as the date field that you specified for the comparison feature.
In the filter bar of the dashboard edit page, specify a time range and click Inquire.
You can also modify the date range.
View SQL statements.
If Comparison Type is set to Dynamic Comparison, the comparison results are dynamically calculated based on the specified date range. The system calculates the difference between the specified date and the related previous date, and then subtracts the date that corresponds to the minus loop. For example:
- If the specified date is January 10, 2020, the date for comparison is January 9, 2020.
- If the specified time range is January 1, 2020 to January 10, 2020, the time range for comparison is December 22, 2019 to December 31, 2019.
1. Click the icon in the upper-right corner of the cross table.
2. Select View SQL Statements.
  You can view the time ranges for comparison.

Perform the comparison

Move the pointer over the required field in the Columns section and click the icon next to the field.
Choose Comparison > Custom.
In the Comparison Settings dialog box, you can change the value of Data Settings.
Data Settings can be set to Specific Value, Difference From, Percent Difference From, or Proportion.
- Specific Value: shows the specific value in the previous period.
- Difference: shows the difference between the value in the current period and the value in the previous period.
- Percentage Difference: shows the value that is calculated by using the formula (Current period value - Previous period value)/Previous period value × 100%.
  If the percentage difference is a negative value, the system provides three formulas for you to choose from.
- Proportion: shows the value that is calculated by using the formula (Current period value/Previous period value) × 100%.
- Orders
Use case 1:
If the gross merchandise value (GMV) of the current day is 100, the GMV of the previous day is 80, and Comparison Type is set to Compare (Day to Day), the comparison result varies based on the value of Data Settings.
- If Data Settings is set to Specific Value, the comparison result is 80.
- If Data Settings is set to Difference From, the comparison result is 20. Formula: (100 - 80).
- If Data Settings is set to Percent Difference From, the comparison result is 25%. Formula: (100 - 80)/80 × 100%.
- If Data Settings is set to Proportion, the comparison result is 125%. Formula: (100/80) × 100%.
Use case 2:
If the GMV of the current day is 100, the GMV of the previous day is 120, and Comparison Type is set to Compare (Day to Day), the comparison result varies based on the value of Data Settings.
- If Data Settings is set to Specific Value, the comparison result is 120.
- If Data Settings is set to Difference From, the comparison result is -20. Formula: 100 - 120.
- If Data Settings is set to Percent Difference From, the comparison result is -16.67%. Formula: (100 - 120)/120 × 100%.
- GMV_daily ring ratio (ratio percentage)=(100/120)*100%=83.33%

Locate the cause of no comparison result

Method 1: Make sure that the dataset contains the data required for the comparison. For example, if the dataset contains only data of 2020 and does not contain data of 2019, the result of Compare (of Last Year) is -.
Method 2: Make sure that the date field that you specified in the filter bar is of the same granularity as the date field that you selected for comparison. For example, the two date fields are of the month granularity. If one date field is of the year granularity and the other is of the month granularity, no data is obtained.
Method 3: If no date field is configured in the data configuration, you must configure a date field for the filter bar that is associated with the chart and for the custom comparison. Otherwise, the comparison result cannot be obtained.