How to configure the comparison feature for a measure - Quick BI

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

Prerequisites

You have created a dashboard. For more information, see Create a Dashboard.
You have selected a target dataset and configured chart fields. For more information, see Configure Chart Fields.
You have added a filter bar. For more information, see Create a Filter Bar.

Note

The Comparison Date and the corresponding date filter condition must both select a date field. Date placeholders are not supported.

Background information

Periodicity-valued comparison: shows the change compared with the previous statistical period. For example, the comparison between January 2021 and December 2020 is called periodicity-valued comparison.
Interval-valued comparison: shows the change compared with the same period of the previous year. For example, the comparison between January 2021 and January 2020 is called interval-valued comparison.

The support for comparison types for date fields is as follows:

Date Field Category	Example	Comparison Description
Year	Order Date (year)	Compare (Year to Year): compares data between the current year and the previous year, such as 2021 and 2020. Note If a Fiscal Year is configured in the dataset, for example, starting in April, FY24 is from April 1, 2023, to March 31, 2024. In this case, FY24 compared to FY23 is the comparison between April 1, 2023, to March 31, 2024, and April 1, 2022, to March 31, 2023.
Quarter	Order 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. Note If a Fiscal Year is configured in the dataset, for example, starting in April, FY24-Q1 is from April 2023 to June 2023. In this case, FY24-Q1 compared to FY23-Q4 is the comparison between April 2023 to June 2023 and January 2023 to March 2023. 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. Note If a Fiscal Year is configured in the dataset, for example, starting in April, FY24-Q1 is from April 2023 to June 2023. In this case, FY24-Q1 compared to FY23-Q1 is the comparison between April 2023 to June 2023 and April 2022 to June 2022.
Month	Order 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	Order 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	Order 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	Order Date (hour), Order Date (minute), Order Date (second), Order Date (ymdhms)	You can configure a custom comparison.

Limits

If Advanced Calculation is configured on a measure field, the comparison feature is grayed out and inactive.
If the comparison feature is configured on a measure field, Advanced Calculation is grayed out and inactive.

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 Fields tab of the dashboard editing page, add fields and configure the comparison feature.
1. Add fields.
  - In the Dimensions list, find Order Date (day), and double-click or drag it to the Row area.
  - In the Measures list, find Order Amount, and double-click or drag it to the Column area.
    Note
    To view the order amount and its comparisons, add five order amounts to the Column area.
2. Hover over the measure field Order Amount, click the icon on the right, and select Comparison -> Compare (Day to Day).
  Refer to this operation to modify the comparison feature of the measure field Order Amount to Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), and Compare (of Last Year).
  Note
  When setting the comparison feature, the default data setting is Percentage Difference. You can modify the data setting on the comparison feature settings page through Custom Comparison. For detailed parameter explanations, see Perform the comparison.
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 shows how to add a filter bar and configure Order Date (day).
1. Add a filter bar.
2. Select Advanced Configuration.
3. Specify query conditions.
  Note
  The date field specified in the filter bar must be the same as the date field specified for the comparison feature.
On the dashboard editing page, click Query.
You can also modify the date range and perform a query.

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 Fields tab of the dashboard editing page, add fields and configure the comparison feature.
1. Add fields.
  - In the Dimensions list, find Order Date (month), and double-click or drag it to the Row area.
  - In the Measures list, find Order Amount, and double-click or drag it to the Column area.
    Note
    To view the order amount and its comparisons, add four order amounts to the Column area.
2. Hover over the measure field Order Amount, click the icon on the right, and select Comparison -> Compare (Month to Month).
  Refer to this operation to modify the comparison feature of the measure fieldOrder Amount to Compare (Month to Month) and Compare (of Last Year).
  Note
  When setting the comparison feature, the default data setting is Percentage Difference. You can modify the data setting on the comparison feature settings page through Custom Comparison. For detailed parameter explanations, see Perform the comparison.
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 shows how to add a filter bar and configure Order Date (month).
1. Add a filter bar.
2. Select Advanced Configuration.
3. Specify query conditions.
  Note
  The date field specified in the filter bar must be the same as the date field specified for the comparison feature.
On the dashboard editing page, select a time range and click Query.
In this example, data from January 2024 to December 2024 is filtered. The system automatically calculates the month-on-month and year-on-year comparison data.

Multiple date fields in dimensions

When dimensions include multiple date fields, the field with the finest granularity is utilized for the comparison feature.

The example below illustrates the configuration of the comparison feature for a measure using a cross table.

On the Fields tab of the dashboard editing page, add fields.
- In the Dimensions list, locate Order Date (day) and Order Date (month), then double-click or drag them to the Row area.
- In the Measures list, find Order Amount, and double-click or drag it to the Column area.
Hover over the measure field, click the icon on the right, and select Comparison -> Custom.

On the Comparison Settings page, set the following parameters.

Configuration Item	Value	Description
Comparison Date	Order Date (day)	If dimensions contain multiple date fields, the date field of the smallest granularity is used for the comparison feature. In this example, only Order Date (day) can be selected.
Comparison Type	Compare (day To Day) Compare (of Last Week) Compare (of Last Month) Compare (of Last Year)	The value of Comparison Type changes with the value of Comparison Date. In this example, it is Compare (day To Day).
Data Setting	Specific Value Difference Percentage Difference Proportion	If the value is Percentage Difference, you can select the calculation formula for the data.
Interval-valued Comparison (only effective when the filter binding is day)	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. Note If no date field is included in dimensions and Interval-valued Comparison is cleared, you can compare the entire previous month. For more information, see No date field in dimensions (day - Compare (of Last Month)).
Calculation Formula	(current Period Data/previous Period Data-1)×100%: Default value 1-(current Period Data/previous Period Data)×100% Display As -	If Data Setting is Percentage Difference, and only when the previous period data is less than 0, the calculation formula takes effect.

Click Confirm.
Add and configure a filter bar.
1. Add a filter bar.
2. Select Advanced Configuration.
3. Define query conditions.
On the dashboard editing page, select a query range and click Query.

No date field in dimensions (day - Compare (Day to Day))

If dimensions lack a date field, you must select one in the associated filter bar and choose a comparison date with matching time granularity on the Comparison Settings page to enable the comparison feature.

The example below illustrates how to set up the comparison feature for a measure using a cross table.

On the Fields tab of the dashboard editing page, add fields and set up the comparison feature.
1. Add fields.
  - In the Dimensions list, locate Area, and either double-click or drag it to the Row area.
  - In the Measures list, locate Order Amount, and either double-click or drag it to the Column area.
2. Hover over the measure field Order Amount, click the icon on the right, and select Comparison -> Custom.
3. On the Comparison Settings page, set the comparison date, type, and data setting.
  For this example, choose Order Date (day) as the comparison date, Compare (Day to Day) as the comparison type, and Percentage Difference as the data setting.
  Note
  The default data setting for the comparison feature is Percentage Difference. For a detailed explanation of parameters, see Perform the comparison.
4. Click Confirm.
Set up filters.
The next example demonstrates how to add a filter bar and configure Order Date (day).
1. Add a filter bar.
2. Select Advanced Configuration.
3. Specify the query conditions.
  Note
  Ensure the date field in the filter bar matches the one used for the comparison feature.
On the dashboard editing page, click Query.
You can also adjust the date range and execute a query.

No date field in dimensions (day - Compare (of Last Month))

The example below illustrates how to set up the comparison feature for a measure using a cross table.

On the Fields tab of the dashboard editing page, add fields and set up the comparison feature.
1. Add fields.
  - In the Dimensions list, locate Area, and either double-click or drag it to the Row area.
  - In the Measures list, find Order Amount, and either double-click or drag it to the Column area.
2. Hover over the measure field Order Amount, click the icon on the right, and choose Comparison -> Custom.
3. On the Comparison Settings page, set the comparison date, type, and data setting.
  In this example, you should select 'Order Date (day)' for the comparison date, Compare (of Last Month) for the comparison type, and Percentage Difference for the data setting. Also, ensure to deselect Interval-valued Comparison.
  Note
  - The default data setting for the comparison feature is Percentage Difference. For a detailed explanation of parameters, see Perform the comparison.
  - When conducting Compare (of Last Month), if the months have differing numbers of days (e.g., February with 28 days and January with 31 days), selecting Interval-valued Comparison compares data from February 1 to 28 with that from January 1 to 28. Without Interval-valued Comparison, it compares data from February 1 to 28 with that from January 1 to 31.
4. Click Confirm.
Set up filters.
Below is an example of adding a filter bar and configuring Order Date (day).
1. Add a filter bar.
2. Choose Advanced Configuration.
3. Define query conditions.
  Note
  Ensure the date field in the filter bar matches the one used for the comparison feature.
On the dashboard editing page, click Query.
You can also adjust the date range and perform a query.
Since Interval-valued Comparison is not selected and dimensions do not include a date field, querying the entire month will compare it with the full previous month's data; for example, data from February 1 to 28 will be compared with data from January 1 to 31.

No date field in dimensions (year)

If your dimensions lack a date field, you must select one in the associated filter bar and choose a comparison date with matching time granularity on the Comparison Settings page to enable the comparison feature.

The following example demonstrates how to set up the comparison feature for a measure using a cross table.

In the dashboard editor, navigate to the Fields tab to add fields and activate the comparison feature.
1. Add fields.
  - Locate Dimensions, find Area, and either double-click or drag it to the Row section.
  - In the Measures list, locate Order Amount, and double-click or drag it to the Column section.
2. Hover over the measure field Order Amount, click the icon, and choose Comparison > Custom.
3. On the Comparison Settings page, set the comparison date, type, and data option.
  For this example, select Order Date (year) as the comparison date, Compare (Year to Year) as the type, and Difference for the data option.
  Note
  Data options include Specific Value, Difference, Percentage Difference, and Proportion. For more information, see Perform the comparison.
4. Click Confirm.
Set up filters.
This example illustrates how to add a filter bar and configure Order Date (year).
1. Add a filter bar.
2. Choose Advanced Configuration.
3. Define the query conditions.
  Note
  Ensure the date field in the filter bar matches the one used for the comparison feature.
On the dashboard editing page, select a query range and click Query.
You can also adjust the date range and run a new query.

No date field in dimensions (dynamic comparison)

If dimensions lack a date field, you must select one and configure the comparison feature on the Comparison Settings page.

In a manner akin to utilizing multiple date fields in dimensions, the system also enables the configuration of Dynamic Comparison when dimensions lack a date field. The subsequent example illustrates the setup process for dynamic comparison using a cross table.

On the Fields tab of the dashboard editing page, add fields and set up the comparison feature.
1. Add fields.
  - In the Dimensions list, locate Product Package and either double-click or drag it to the Row area.
  - In the Measures list, find Profit Amount and double-click or drag it to the Column area.
2. Hover over the measure field Profit Amount, click the icon on the right, and select Comparison -> Custom.
3. On the Comparison Settings page, set the comparison date, type, and data setting.
  For this example, choose Order Date (day) as the comparison date, Dynamic Comparison as the type, and Percentage Difference for the data setting.
  Note
  Data settings include options like Specific Value, Difference, Percentage Difference, and Proportion. For a detailed explanation of parameters, see Perform the comparison.
4. Click Confirm.
Set up filters.
The example below illustrates how to add a filter bar and configure Order Date (day).
1. Add a filter bar.
2. Choose Advanced Configuration.
3. Define query conditions.
  Note
  Ensure the date field in the filter bar matches the one used for the comparison feature.
On the dashboard editing page, choose a query range and click Query.
You may also adjust the date range and execute a query.
Examine SQL statements.
With Comparison Type set to Dynamic Comparison, the system dynamically calculates comparison values based on the selected date range in the filter, determining the difference between the specified date and its preceding date, then subtracting the corresponding date in the minus loop. For instance:
- If the specified date is January 10, 2020, the comparison date is January 9, 2020.
- If the selected time range is from January 1, 2020, to January 10, 2020, the comparison range is December 22, 2019, to December 31, 2019.
1. Hover over the upper-right corner of the cross table and click the icon.
2. Select View SQL.
  This reveals the interval used for dynamic comparison.

Perform the comparison

Hover over the measure field and click the icon on the right.
Select Comparison -> Custom.
On the Comparison Settings page, you can modify the data settings.
Data settings include options such as Specific Value, Difference, Percentage Difference, and Proportion. These options are defined as follows:
- Specific Value displays the exact value from the previous period.
- Difference = Current period value - Previous period value.
- Percentage Difference = (Current period value - Previous period value) / Previous period value × 100%.
  For negative percentage differences, the system offers three calculation formulas for user selection.
- Proportion = (Current period value / Previous period value) × 100%.
- Order
Example Case 1:
If today's gross merchandise value (GMV) is 100 and yesterday's was 80, with the Comparison Type set to Compare (Day to Day), the comparison results will differ based on the Data Settings:
- GMV_Compare (Day to Day) (Specific Value) = 80.
- GMV_Compare (Day to Day) (Difference) = 100 - 80 = 20.
- GMV_Compare (Day to Day) (Percentage Difference) = (100 - 80) / 80 × 100% = 25%.
- GMV_Compare (Day to Day) (Proportion) = (100/80) × 100% = 125%
Example Case 2:
If today's GMV is 100 and yesterday's was 120, with the Comparison Type set to Compare (Day to Day), the comparison results will differ based on the Data Settings:
- GMV_Compare (Day to Day) (Specific Value) = 120.
- GMV_Compare (Day to Day) (Difference) = 100 - 120 = -20.
- GMV_Compare (Day to Day) (Percentage Difference) = (100 - 120) / 120 × 100% = -16.67%.
- GMV_Compare (Day to Day) (Proportion) = (100 / 120) × 100% = 83.33%.

Locate the cause of no comparison result

Method 1: Verify that the comparison date in your dataset contains data. For instance, if the dataset includes data for 2020 but lacks data for 2019, the year-on-year comparison will show a -.
Method 2: Ensure that the date field you have chosen in the filter bar matches the granularity of the date field selected for comparison. For example, both date fields should be at the month granularity. A mismatch, such as one field at year granularity and the other at month granularity, will result in no data being retrieved.
Method 3: If a date field is not set up in the data configuration, you must configure one for the filter bar associated with the chart and for the custom comparison to retrieve comparison results.

Please provide the technical excerpt you would like to have polished.