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

Prerequisites

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:
  • Comparison types for a date field of the day granularity, which is in the yyyymmdd format:
    • 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.
  • Comparison types for a date field of the week granularity, which is in the yyyy-ww format:
    • 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.
  • Comparison types for a date field of the month granularity, which is in the yyyymm format:
    • 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.
  • Comparison types for a date field of the quarter granularity, which is in the yyyyqq format:
    • 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.
  • Comparison types for a date field of the year granularity, which is in the yyyy format:
    • Compare (Year to Year): compares data between the current year and the previous year, such as 2021 and 2020.
  • The comparison feature is not supported for date fields in the hh, hh:mm, hh:mm:ss, or yyyymmdd hh:mm:ss format.
Date field type Example Option under Comparison
Year order_date(year)
  • Compare (Year to Year)
  • Custom
  • None
Quarter order_date(quarter)
  • Compare (Quarter to Quarter)
  • Compare (of Last Year)
  • Custom
  • None
Month order_date(month)
  • Compare (Month to Month)
  • Compare (of Last Year)
  • Custom
  • None
Day order_date(day)
  • Compare (Day to Day)
  • Compare (of Last Week)
  • Compare (of Last Month)
  • Compare (of Last Year)
  • Custom
  • None
Hour, minute, or second order_date(hour), order_date(minute), or order_date(second) The comparison feature is not supported.
YYYY/MM/DD HH:mm:ss order_date(ymdhms)
  • Custom
  • None

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 (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.

  1. On the Data tab of the dashboard editing page, add data and configure the comparison feature.
    1. Drag the order_date(day) field from the Dimensions list to the Rows section and drag five order_amt fields from the Measures list to the Columns section.
    2. Move the pointer over an order_amt field in the Columns section, click the 1 icon next to the field, and then choose Comparison > Compare (Day to Day).
      You can perform this step to change the comparison type for the order_amt field to Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), or Compare (of Last Year). Configure the comparison feature-3.11
      Note In the comparison settings, the default value of Data Settings is Percentage Difference. 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 Perform the comparison.
    3. Click Update.
  2. Optional:Configure filters.
    If you want to filter data that is generated in a specific time interval, perform this step. The following figure shows how to add a filter bar and configure filters for the order_date(day) field. Configure a filter-1
    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.
  3. In the filter bar of the dashboard editing page, click Inquire.
    You can also modify the date range. Day-on-day comparison-3.11

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.

  1. On the Data tab of the dashboard editing page, add data and configure the comparison feature.
    1. Drag the order_date(month) field from the Dimensions list to the Rows section and drag three order_amt fields from the Measures list to the Columns section.
    2. Move the pointer over the order_amt field in the Columns section, click the 1 icon next to the field, and then choose Comparison > Compare (Month to Month).
      You can perform this step to change the comparison type for the order_amt field to Compare (Month to Month) or Compare (of Last Year). Configure month-on-month comparison-3.11
      Note In the comparison settings, the default value of Data Settings is Percentage Difference. 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 Perform the comparison.
    3. Click Update.
  2. Optional:Configure filters.
    If you want to filter data that is generated in a specific time interval, perform this step. The following figure shows how to add a filter bar and configure filters for the order_date(month) field. Specify a time range-311
    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.
  3. In the filter bar of the dashboard editing page, specify a time range and click Inquire.
    In this example, the data from January 2014 to December 2014 is filtered, and the system automatically calculates the data of Compare (Month to Month) and Compare (of Last Year). Month-on-month comparison-3.11

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.

  1. On the dashboard editing page, add and configure a cross table, as shown in the following figure.
    In this example, date fields order_date(day) and order_date(month) are added to the Rows section. Configure multiple date fields-3.11
  2. Move the pointer over the required field in the Columns section and click the 1 icon next to the field.
  3. Choose Comparison > Custom.
  4. In the Comparison Settings dialog box, configure the parameters that are described in the following table.
    Parameter Valid value Description
    Comparison Date order_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 order_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
    • Percentage Difference
    If you set this parameter to Percentage Difference, you must click Advanced Settings and select a formula.
    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 Percentage Difference and Value of Previous Period is less than 0.
  5. Click OK.
  6. Add and configure a filter bar, as shown in the following figure.
    2
  7. In the filter bar of the dashboard editing page, specify a time range and click Inquire.
    Time dimension-3.11

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.

  1. On the Data tab of the dashboard editing page, add data and configure the comparison feature.
    1. Drag the area field from the Dimensions list to the Rows section and drag the order_amt field from the Measures list to the Columns section.
    2. Move the pointer over the order_amt field in the Columns section, click the 1 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 order_date(day), Comparison Type is set to Compare (Day to Day), and Data Settings is set to Percentage Difference. Comparison-3.11
      Note In the comparison settings, the default value of Data Settings is Percentage Difference. For more information about the parameters, see Perform the comparison.
    4. Click OK.
  2. Configure filters.
    The following figure shows how to add a filter bar and configure filters for the order_date(day) field. Configure filters-2
    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.
  3. In the filter bar of the dashboard editing page, click Inquire.
    You can also modify the date range. No date-3.11

No date field in dimensions (year)

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 parameters of the comparison feature for a measure. A cross table is used in this example.

  1. On the Data tab of the dashboard editing page, add data and configure the comparison feature.
    1. Drag the area field from the Dimensions list to the Rows section and drag the order_amt field from the Measures list to the Columns section.
    2. Move the pointer over the order_amt field in the Columns section, click the 1 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 order_date(year), Comparison Type is set to Compare (Year to Year), and Data Settings is set to Difference. Comparison-3.11
      Note Data Settings can be set to Specific Value, Difference, or Percentage Difference. For more information about the parameters, see Perform the comparison.
    4. Click OK.
  2. Configure filters.
    The following figure shows how to add a filter bar and configure filters for the order_date(year) field. Configure filters
    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.
  3. In the filter bar of the dashboard editing page, specify a time range and click Inquire.
    You can also modify the date range. Year granularity-3.11

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.

If no date fields are contained in dimensions, you can also set Comparison Type to Dynamic Comparison, which is not supported in Multiple date fields in dimensions. The following example shows how to configure dynamic comparison. A cross table is used in this example.

  1. On the Data tab of the dashboard editing page, add data and configure the comparison feature.
    1. Drag the product_box field from the Dimensions list to the Rows section and drag the profit_amt field from the Measures list to the Columns section.
    2. Move the pointer over the profit_amt field in the Columns section, click the 1 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 order_date(day), Comparison Type is set to Dynamic Comparison, and Data Settings is set to Percentage Difference. 123
      Note Data Settings can be set to Specific Value, Difference, or Percentage Difference. For more information about the parameters, see Perform the comparison.
    4. Click OK.
  2. Configure filters.
    The following figure shows how to add a filter bar and configure filters for the order_date(day) field. Configure filters-2
    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.
  3. In the filter bar of the dashboard editing page, specify a time range and click Inquire.
    You can also modify the date range. Dynamic comparison-3.11
  4. 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. 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 1 icon in the upper-right corner of the cross table.
    2. Select View SQL Statements.
      You can view the time ranges for comparison. 3

Perform the comparison

  1. Move the pointer over the required field in the Columns section and click the 1 icon next to the field.
  2. Choose Comparison > Custom.
  3. In the Comparison Settings dialog box, you can change the value of Data Settings.
    Data Settings can be set to Specific Value, Difference, or Percentage Difference.
    • 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. Configure the comparison feature-3.11

    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, the comparison result is 20. Formula: (100 - 80).
    • If Data Settings is set to Percentage Difference, the comparison result is 25%. Formula: (100 - 80)/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, the comparison result is -20. Formula: 100 - 120.
    • If Data Settings is set to Percentage Difference, the comparison result is -16.67%. Formula: (100 - 120)/120 × 100%.

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.