Quick BI:Comparison

Configuration Entry

Take a cross table as an example. Hover over a measure field, click the icon on the right, select Advanced Calculation -> Comparison, and select the comparison type you need based on your business scenario. You can configure Custom comparison and Quick comparison,

Features

Custom Comparison

1. Hover over a measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Custom.

2. On the Comparison Settings page, configure the following parameters.

   Parameter	Description
   Date For Comparison	Date for Comparison is the reference date field for calculating the comparison: If dimensions contain multiple date fields, the field of the smallest granularity is used and cannot be modified. If dimensions do not contain date fields, you can select any date field from the dataset for comparison (in this case, the calculation of the comparison depends on the filter condition of this date field).You can select any date field in the dataset for comparison (in this case, the comparison calculation depends on the filter condition of this date field) The Date for Comparison supports the following date granularities: date (day), date (week), date (month), date (quarter), and date (year).
   Comparison Type	The value of Comparison Type varies with the value of Date for Comparison. The following are some examples. For complete information, see Supported Comparison Types for Date Fields. Compare (Day to Day): When the date for comparison is day, it compares the current day with the previous day, such as December 5, 2024 and December 5, 2023. Compare (of Last Week): When the date for comparison is day, it compares the current day with the same day of the previous week, such as December 5, 2024 (Thursday) and November 28, 2024 (last Thursday). Compare (of Last Month): When the date for comparison is day, it compares the current day with the same day of the previous month, such as December 5, 2024 and November 5, 2024. Compare (of Last Year): When the date for comparison is day, it compares the current day with the same day of the previous year, such as December 5, 2024 and December 5, 2023. Dynamic Comparison: When dimensions do not contain date fields, you can configure dynamic comparison.
   Data Settings	Data Settings supports Specific Value, Difference From, Percent Difference From, and Proportion: Specific Value: displays the specific value of the previous period Difference From = Current period value - Previous period value Percent Difference From = (Current period value - Previous period value)/Previous period value × 100% If the percent difference is negative, you can select one of the following calculation formulas: (Current Period Data/Previous Period Data - 1) × 100%: default value 1 - (Current Period Data/Previous Period Data) × 100% Display As - Proportion = (Current period value/Previous period value) × 100% Example 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: GMV compare (day to day) (value)=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%. Example 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: GMV compare (day to day) (value)=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%. If Data Settings is set to Proportion, the comparison result is 83.33%. Formula: (100/120) × 100%.
   Same Period Comparison (Takes effect only when the query filter is bound to day granularity)	If you configure a filter condition for the "Date for Comparison" field, the filtered data is the data of the "current period". The system will push forward based on the comparison configuration to obtain the "comparison period" and retrieve the data of the "comparison period". For example: If the filter condition is 2022-01-02, when calculating the year-on-year comparison, the comparison period is 2021-01-02. The Same Period Comparison option takes effect only when the filter condition is bound to the "Date for Comparison" with day granularity. In this case, the comparison period is pushed forward, and the filter condition of day granularity is pushed forward according to the "Same Period Comparison" method. For example, if the filter condition is day and "Same Period Comparison" is selected, you can achieve the effect of comparing with the same period of the previous month, such as comparing 2024.04.01~2024.04.30 with 2024.03.01~2024.03.30. Note When the chart contains a date field of day granularity, Same Period Comparison is selected by default. When Same Period Comparison is not selected, or when it is selected but the query filter is not of day granularity, it is Non-Same Period Comparison. When both the filter condition granularity and the comparison date granularity are day, it is Non-Same Period Comparison, and the chart does not contain date fields, the comparison period is pushed forward, and it is a full month comparison, such as: 0901-0930 is pushed forward to 0801-0831. For more information, see Day granularity - Compare (of Last Month). For information about the logic of pushing forward the filter period, see Logic of Pushing Forward the Filter Period.

Quick Comparison

When the chart contains only one date field, quick comparison is supported. The default settings for quick comparison are "Percent Difference From" and "Same Period Comparison".

Hover over a measure field, click the icon on the right, select Advanced Calculation -> Comparison, and select the quick comparison type you need to configure.

The comparison types available for quick configuration vary with the date granularity. For more information, see Supported Comparison Types for Date Fields.

Logic of Pushing Forward the Filter Period

• When the filter condition and the comparison date granularity are different, the comparison period is not pushed forward.

• When the filter condition and the comparison date granularity are the same, the comparison period is pushed forward by default, and it is a complete period push-forward. For example, if the filter condition is month, quarter, or year, you can achieve a full month comparison, such as comparing 2024.04.01~2024.04.30 with 2024.03.01~2024.03.31.

  If the filter condition is week: It is flattened to day granularity and pushed forward.

When the filter condition granularity >= comparison granularity, it is a complete period comparison (except for week granularity). The specific comparison logic is as follows. You can verify it with Scenario 1 in Week granularity - Compare (of Last Year):

When the filter condition granularity < comparison granularity, it is a non-complete period comparison. The specific comparison logic is as follows. You can verify it with Scenario 2 in Week granularity - Compare (of Last Year).

Examples

Dimensions Contain 1 Date Field

Day granularity

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 dashboard editing page, go to the Fields tab, add fields, and configure the comparison feature.

   1. Add fields.

      • In the Dimensions list, find Shipping_date(day), and double-click it or drag it to the Rows section.

      • In the Measures list, find order amount, and double-click it or drag it to the Columns section.

        Note

        Because you need to view the order amount and its day-to-day comparison, week-on-week comparison, month-on-month comparison, and year-on-year comparison, add 5 order amounts to the Columns section.

   2. Hover over the order amount measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Compare (Day to Day).

      Follow this operation to modify the comparison type of the order amount measure field to Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), and Compare (of Last Year).

      Note

      When you configure the comparison feature, Percent Difference From is selected for Data Settings by default. You can select Custom Comparison and modify Data Settings on the Comparison Settings page.

   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 example shows how to add a filter bar and configure shipping_date(day).

   1. Add a filter bar.

   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.

3. On the dashboard editing page, click Query.

   You can also modify the date range and perform a query.

Month granularity

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 dashboard editing page, go to the Fields tab, add fields, and configure the comparison feature.

   1. Add fields.

      • In the Dimensions list, find shipping_date(month), and double-click it or drag it to the Rows section.

      • In the Measures list, find order amount, and double-click it or drag it to the Columns section.

        Note

        Because you need to view the order amount and its month-to-month comparison and year-on-year comparison, add 4 order amounts to the Columns section.

   2. Hover over the order amount measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Compare (Month to Month).

      Follow this operation to modify the comparison type of the order amount measure field to Compare (Month to Month) and Compare (of Last Year).

      Note

      When you configure the comparison feature, Percent Difference From is selected for Data Settings by default. You can select Custom Comparison and modify Data Settings on the Comparison Settings page.

   3. Click Update.

2. (Optional)

   Add and configure 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.

3. On the dashboard editing page, select a time range and click Query.

   In this example, data from 2024-01 to 2024-12 is filtered, and the system automatically calculates the month-to-month comparison and year-on-year comparison data.

DimensionsCcontain Multiple Date Fields

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, go to the Fields tab and add fields.

   • In the Dimensions list, find Shipping_date(day) and shipping_date(month), and double-click them or drag them to the Rows section in sequence.

   • In the Measures list, find order amount, and double-click it or drag it to the Columns section.

2. Hover over the measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Custom.

3. On the Comparison Settings page, configure the following parameters.

   

4. Click OK.

5. Add and configure query conditions.

6. On the dashboard editing page, select a query range and click Query.

   

Dimensions do not Contain Date Fields

Hour-minute-second granularity - compare (day to day)

Because the hour-minute-second granularity contains only time information but not date information, you cannot directly configure the comparison feature for the hour-minute-second granularity. However, you can configure the day-to-day comparison for the day granularity to compare data with the same time point of the previous day. The following example shows how to configure the comparison feature for the hour-minute-second granularity. A cross table is used in this example.

1. On the dashboard editing page, go to the Fields tab and add fields.

   • In the Dimensions list, find Date(minute), and double-click it or drag it to the Rows section.

   • In the Measures list, find order amount, and double-click it or drag it to the Columns section.

2. Hover over the measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Custom.

3. On the Comparison Settings page, configure the following parameters.

   

4. Click OK.

5. Add and configure query conditions.

   

6. On the dashboard editing page, select a query range and click Query.

   

Day granularity - compare (day to day)

If dimensions do not contain date fields, you need to select a date field in the associated filter bar and select a date field of the same time granularity as the date field in the filter bar on the Comparison Settings page to configure 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, go to the Fields tab, add fields, and configure the comparison feature.

   1. Add fields.

      • In the Dimensions list, find Region, and double-click it or drag it to the Rows section.

      • In the Measures list, find order amount, and double-click it or drag it to the Columns section.

   2. Hover over the order amount measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Custom.

   3. On the Comparison Settings page, configure Date for Comparison, Comparison Type, and Data Settings.

      In this example, shipping_date(day) is selected for Date for Comparison, Compare (Day To Day) is selected for Comparison Type, and Percent Difference From is selected for Data Settings.

      Note

      When you configure the comparison feature, Percent Difference From is selected for Data Settings by default.

   4. Click OK.

2. Add and configure 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.

3. On the dashboard editing page, click Query.

   You can also modify the date range and perform a query.

Day granularity - compare (of last month)

If dimensions do not contain date fields, you need to select a date field in the associated filter bar and select a date field of the same time granularity as the date field in the filter bar on the Comparison Settings page to configure 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, go to the Fields tab, add fields, and configure the comparison feature.

   1. Add fields.

      • In the Dimensions list, find Region, and double-click it or drag it to the Rows section.

      • In the Measures list, find order amount, and double-click it or drag it to the Columns section.

   2. Hover over the order amount measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Custom.

   3. On the Comparison Settings page, configure Date for Comparison, Comparison Type, and Data Settings.

      In this example, shipping_date(day) is selected for Date for Comparison, Compare (of Last Month) is selected for Comparison Type, Percent Difference From is selected for Data Settings, and Same Period Comparison is not selected.

      Note

      • When you configure the comparison feature, Percent Difference From is selected for Data Settings by default.

      • When calculating Compare (of Last Month) and the number of days in the months is different (for example, February has 28 days and January has 31 days), if Same Period Comparison is selected, the data from February 1, 2023 to February 28, 2023 is compared with the data from January 1, 2023 to January 28, 2023. If Same Period Comparison is not selected, the data from February 1, 2023 to February 28, 2023 is compared with the data from January 1, 2023 to January 31, 2023.

   4. Click OK.

2. Add and configure 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.

3. On the dashboard editing page, click Query.

   You can also modify the date range and perform a query.

   In this case, because Same Period Comparison is not selected and dimensions do not contain date fields, if the date query condition is a full month, the month-on-month comparison compares data with the full month of the previous month, that is, the data from February 1 to February 28 is compared with the data from January 1 to January 31.

Year granularity - compare (year to year)

If dimensions do not contain date fields, you need to select a date field in the associated filter bar and select a date field of the same time granularity as the date field in the filter bar on the Comparison Settings page to configure 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, go to the Fields tab, add fields, and configure the comparison feature.

   1. Add fields.

      • In the Dimensions list, find Region, and double-click it or drag it to the Rows section.

      • In the Measures list, find order amount, and double-click it or drag it to the Columns section.

   2. Hover over the order amount measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Custom.

   3. On the Comparison Settings page, configure Date for Comparison, Comparison Type, and Data Settings.

      In this example, shipping_date(year) is selected for Date for Comparison, Compare (Year To Year) is selected for Comparison Type, and Difference From is selected for Data Settings.

      Note

      Data Settings supports Specific Value, Difference From, Percent Difference From, and Proportion.

   4. Click OK.

2. Add and configure query conditions.

   The following example shows how to add a filter bar and configure shipping_date(year).

   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.

3. On the dashboard editing page, select a query range and click Query.

   You can also modify the date range and perform a query.

Week granularity - compare (of last year)

The following examples describe the logic of pushing forward the filter period in several typical scenarios.

Scenario 1

Take a cross table as an example.

1. On the dashboard editing page, go to the Fields tab, add fields, and configure the comparison feature.

   1. Add fields.

      • In the Dimensions list, find Region, and double-click it or drag it to the Rows section.

      • In the Measures list, find order amount, and double-click it or drag it to the Columns section.

        Note

        To easily view the order amount and year-on-year comparison of the current period and the comparison period, you can add 3 order amounts to the Columns section.

   2. Hover over the order amount measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Custom.

   3. On the Comparison Settings page, configure Date for Comparison, Comparison Type, and Data Settings.

      In this example, shipping_date(week) is selected for Date for Comparison, Compare (of Last Year) is selected for Comparison Type, Specific Value and Percent Difference From are selected for Data Settings, and Same Period Comparison is selected.

   4. Click OK.

2. Configure filters.

   The following example shows how to add a filter bar and configure shipping_date(day).

   1. Add a filter bar.

   2. Select Advanced Configuration.

   3. Set query conditions.

3. On the dashboard editing page, click Query.

   You can also modify the date range and perform a query.

   

   Now, we can see that the current period is from 2020 to 2024, that is, from 2020-01 week Wednesday (January 1, 2020) to 2025-01 week Tuesday (December 31, 2024), and the total order amount is 18.31 million. The comparison period is from 2019-01 week Wednesday (January 2, 2019) to 2024-01 week Tuesday (January 2, 2024), and the order amount is 17.82 million. The year-on-year comparison is 2.75%.

   

Scenario 2

Take a cross table as an example.

1. On the dashboard editing page, go to the Fields tab, add fields, and configure the comparison feature.

   1. Add fields.

      • In the Dimensions list, find Region, and double-click it or drag it to the Rows section.

      • In the Measures list, find order amount, and double-click it or drag it to the Columns section.

        Note

        To easily view the order amount and year-on-year comparison of the current period and the comparison period, you can add 3 order amounts to the Columns section.

   2. Hover over the order amount measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Custom.

   3. On the Comparison Settings page, configure Date for Comparison, Comparison Type, and Data Settings.

      In this example, shipping_date(week) is selected for Date for Comparison, Compare (of Last Year) is selected for Comparison Type, Specific Value and Percent Difference From are selected for Data Settings, and Same Period Comparison is selected.

   4. Click OK.

2. Add and configure query conditions.

   

3. On the dashboard editing page, click Query.

   You can also modify the date range and perform a query.

   

   Now, we can see that the current period is from January 1, 2020 to May 22, 2020, that is, from 2020-01 week Wednesday (January 1, 2020) to 2020-21 week Friday (May 22, 2020), and the total order amount is 1.792 million. The comparison period is from 2019-01 week Wednesday (January 2, 2019) to 2020-21 week Friday (May 24, 2020), and the order amount is 511,300. The year-on-year comparison is 250.44%.

   

Dynamic Comparison

If dimensions do not contain date fields, you need to select a date field on the Comparison Settings page and configure the comparison feature.

Similar to DimensionsCcontain Multiple Date Fields, if dimensions do not contain date fields, the system also supports configuring Dynamic Comparison. The following example shows how to configure dynamic comparison. A cross table is used in this example.

1. On the dashboard editing page, go to the Fields tab, add fields, and configure the comparison feature.

   1. Add fields.

      • In the Dimensions list, find Product Package, and double-click it or drag it to the Rows section.

      • In the Measures list, find profit amount, and double-click it or drag it to the Columns section.

   2. Hover over the profit amount measure field, click the icon on the right, and select Advanced Calculation -> Comparison -> Custom.

   3. On the Comparison Settings page, configure Date for Comparison, Comparison Type, and Data Settings.

      In this example, shipping_date(day) is selected for Date for Comparison, Dynamic Comparison is selected for Comparison Type, and Percent Difference From is selected for Data Settings.

      Note

      Data Settings supports Specific Value, Difference From, Percent Difference From, and Proportion.

   4. Click OK.

2. Add and configure 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.

3. On the dashboard editing page, select a query range and click Query.

   You can also modify the date range and perform a query.

4. View SQL statements.

   When Comparison Type is set to Dynamic Comparison, the system dynamically calculates the comparison value based on the date range selected in the filter condition. The system first 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. Hover over the upper-right corner of the cross table and click the icon.

   2. Select View SQL.

      You can see the range of dynamic comparison.

Special Scenarios

When you configure the comparison feature and perform drill-down, dimension group switching, or field filtering operations on the chart, the comparison configuration will be adjusted according to the field changes. The specific adjustment rules are as follows.

Drill-down

When you perform a drill-down operation, the comparison feature will change to the corresponding granularity. For example, when you drill down from 2021 to quarters of 2021, the year-to-year comparison automatically changes to quarter-to-quarter comparison.

The specific rules are as follows: After drilling down, the periodicity-valued comparison changes to the corresponding granularity's periodicity-valued comparison, and the interval-valued comparison remains as interval-valued comparison.

Dimension Group Switching

When you configure dimension groups in a chart and switch dimensions, the comparison calculation will follow the granularity change. For example, when you switch the date dimension group from year to quarter, the year-to-year comparison will automatically switch to quarter-to-quarter comparison.

The specific rules are as follows: The "-" in the table below indicates that there is no corresponding comparison configuration. When switching, the comparison will be canceled, and only basic data will be displayed (if it is a secondary indicator, the secondary indicator will be cleared).

Field Filtering

When you filter fields in the Selected Fields of the chart, the system will find the date field with the smallest granularity after filtering and set the corresponding comparison calculation. For example, if there are date fields of year, quarter, month, week, and day granularities, the comparison is currently set to day-to-day comparison. After filtering out the day granularity field, the chart will automatically display week-to-week comparison.

The specific rules are the same as those for dimension group switching.

Locate the cause of no comparison result

• Method 1: Make sure that the dataset contains data for the date for comparison. For example, if the dataset contains only data for 2020 but not for 2019, the year-on-year comparison result is displayed as -.

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