Quick BI:Period-over-period and same-period comparisons

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

Procedure

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

2. In the Comparison Settings window, configure the parameters.

   Parameter	Description
   Date Field	The Date field 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 field supports the following granularities: Date (day), Date (week), Date (month), Date (quarter), and Date (year).
   Comparison Type	The value of Comparison type changes based on the value of Date field. The following list provides some examples. For a complete list, 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: If the chart dimensions do not include a date field, you can configure a dynamic comparison. The comparison value is calculated dynamically based on the date filter condition selected in the query control. For example, if the date filter is from January 1, 2020 to January 10, 2020, the comparison period is from December 22, 2019 to December 31, 2019. Custom: Set a custom comparison date based on your business needs. Note The number entered for the comparison date must be a positive integer. The default value is 1, and the maximum value is 10,000. The available granularities for the comparison date depend on the granularity of the date field, as follows. Daily granularity (day): day, week, month, and year. Weekly granularity (week): week and year. Monthly granularity (month): month and year. Quarterly granularity (quarter): quarter and year. Yearly granularity (year): year. The calculation logic for the custom comparison is as follows. If the chart shows a single date, that date is shifted backward. If the chart shows a date range, the entire range is shifted backward. For more information about the backward shift logic for custom comparison dates, see Filter period backward shift logic. Advanced Settings: Only available when the date field has a day granularity. You can set the comparison date to Start of this month, End of last month, Start of this quarter, End of last quarter, Start of this year, or End of last year.
   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 field, the filtered data is the data of the "current period". The system pushes the period back based on the comparison configuration to get the "comparison period" and retrieves the data for that 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 field 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

DayGranularity

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. In the Comparison Settings window, configure the date field, comparison type, and data settings.

      In this example, Date for Comparison is set to order date (day), Comparison Type is set to Compare (Day To Day), and Data Settings is set to Percent Difference From.

      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. In the Comparison Settings window, configure the date field, comparison type, and data settings.

      In this example, set Date for Comparison to shipping_date(day), Comparison Type to Compare (with Same Day Last Month), and Data Settings to Percentage Difference. Ensure that 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) for Comparison Type, and Difference From 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. In the Comparison Settings window, configure the date field, comparison type, and data settings.

      In this example, the Comparison Date is shipping_date(week), the Comparison Type is Year-over-year, the Data Settings are Specific Value and Percentage Difference, 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. In the Comparison Settings window, configure the date field, comparison type, and data settings.

      In this example, Comparison Date is set to order date (week), Comparison Type is set to Year-over-year, and Data Settings is set to Specific Value and Percentage Difference. The Same Period Comparison option is also 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. In the Comparison Settings window, configure the date field, comparison type, and data settings.

      In this example, shipping_date(day) is selected for Date for Comparison, Dynamic Comparison for Comparison Type, and Percent Difference From 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.

Custom Configuration

Custom configuration lets you set a comparison date based on your specific needs. The following example shows how to configure a comparison for the Order amount field with data from 2 months ago in a cross table.

1. On the dashboard edit page, on the Fields tab, add fields and configure the comparison.

   1. Add fields.

      • In the Dimensions list, find Area and Order Date (month), and then double-click or drag them to the Rows area.

      • In the Measures list, find Order amount, and double-click or drag it to the Columns area. In this example, add two Order amount measures to the Columns area.

   2. Hover over the Order amount measure, click the icon, and select Advanced Calculation > Comparison > Custom.

   3. In the Comparison Settings window, configure the date field, comparison type, and data settings.

      In this example, set the date field to Order Date (month), the comparison type to Custom, the comparison date to 2 Months Ago, and the data setting to Comparison Difference.

      Note

      Data settings support Specific Value, Difference From, Percent Difference From, and Percentage Of.

   4. Click OK and then click Update to update the chart.

2. Add and configure query conditions.

   Note

   The date field configured in the query control must be the same as the date field in the comparison settings. In this example, both must be Order Date (month).

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

   

Advanced Configuration

When the Date field in the Comparison Settings window has a day granularity, you can use the Advanced configuration option. This lets you set the Comparison date to Start of this month, End of last month, Start of this quarter, End of last quarter, Start of this year, or End of last year. The following example shows how to set a comparison for the Order amount field relative to the data at the Start of this month in a cross table.

1. On the dashboard edit page, on the Fields tab, add fields and configure the comparison.

   1. Add fields.

      • In the Dimensions list, find Area and Order Date (day), and then double-click or drag them to the Rows area.

      • In the Measures list, find Order amount, and double-click or drag it to the Columns area. In this example, add two Order amount measures to the Columns area.

   2. Hover over the Order amount measure, click the icon, and select Advanced Calculation > Comparison > Custom.

   3. In the Comparison Settings window, configure the date field, comparison type, and data settings.

      In this example, the comparison date field is set to Order Date (day), the comparison type is set to Advanced Configuration, the comparison date is set to Beginning Of This Month, and Data Settings is set to Difference.

      Note

      Data settings support Specific Value, Difference From, Percent Difference From, and Percentage Of.

   4. Click OK and then click Update to update the chart.

2. Add and configure query conditions.

   Note

   The date field configured in the query control must be the same as the date field used for the comparison.

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

   

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.