All Products
Search

This Product

    Quick BI:Date Cumulative

    Document Center

    Quick BI:Date Cumulative

    Last Updated:May 22, 2024

    This topic describes how to configure the date rollup of a measure.

    Prerequisites

    Background information

    Date Cumulative calculates the sum of data from the initial date to the current date.

    The following table describes the support for date cumulative calculation.

    Correspondence between date fields and cumulative dates

    Date Field Type

    Example

    Cumulative Date

    Year (in the format of yyyy)

    shipping_date(year)

    • Yearly Cumulative Value: You can calculate the data from the first year of a specific date to the current year, such as 2013 to 2021.

    Quarter (in the format of yyyyqq)

    shipping_date(quarter)

    • Current Year Cumulative Value: You can calculate the data from the first quarter of the current year to the current quarter, such as from the first quarter of 2021 to the second quarter of 2021.

    • Yearly Cumulative Value: You can calculate the data from the first quarter of a specific year to the current quarter, such as from the first quarter of 2013 to the second quarter of 2021.

    Month (in the format of yyyymm)

    shipping_date(month)

    • Quarterly Cumulative Value: You can calculate the data from the first month of the current quarter to the current month, such as from April 2021 to May 2021.

    • Current Year Cumulative Value: You can calculate the data from the first month of the current year to the current month, such as from January 2021 to May 2021.

    • Yearly Cumulative Value: You can calculate the data from the first month of a specific year to the current month, such as from January 2013 to May 2021.

    Week (in the format of yyyy-ww)

    report_date(week)

    • Current Year Cumulative Value: You can calculate the data from the first week of the current year to the current week, such as from the first week of 2021 to the twenty-first week of 2021.

    • Yearly Cumulative Value: You can calculate the data from the first week of a specific year to the current week, such as from the first week of 2013 to the twenty-first week of 2021.

    Day (in the format of yyyymmdd)

    shipping_date(day)

    • Monthly Cumulative Value: You can calculate the data from the first day of the current month to the current day, such as from May 1, 2021 to May 29, 2021.

    • Quarterly Cumulative Value: You can calculate the data from the first day of the current quarter to the current day, such as from April 1, 2021 to May 29, 2021.

    • Current Year Cumulative Value: You can calculate the data from the first day of the current year to the current day, such as from January 1, 2021 to May 29, 2021.

    • Yearly Cumulative Value: You can calculate the data from the first day of a specific year to the current day, such as from January 1, 2013 to May 29, 2021.

    Hour (in the format of hh), Minute (in the format of hh:mm), Second (in the format of hh:mm:ss), and date field in the format of yyyymmdd hh:mm:ss

    report_date(hour), report_date(minute), report_date(second), report_date(ymdhms)

    You cannot configure date accumulation.

    Limits

    • The Comparison option is not supported for measures.

    • Only the field (image) of the summation metric supports the date rollup configuration.

    • The date field on the dimension field is the date field (that is, the field in the hh format), minute (that is, the field in the hh:mm format), second (that is, the field in the hh:mm:ss format), year, month, day, hour, minute, second (that is, the field in the yyyymmdd hh:mm:ss format). You cannot configure date rollup.

    • If there is no date field on the dimension, you cannot configure date rollup.

    Example of configuring a cumulative date

    Dimension contains date fields (daily granularity)

    The following example uses a cross table to configure date rollup.

    1. On the Field tab of the dashboard edit page, add fields.

      • In the Dimension list, find shipping_date(day) and double-click or drag this dimension to the Rows section.

      • In the measure list, find order_amt and double-click or drag this measure to the Columns section.

        Note

        To view the monthly, quarterly, current-year, and calendar-year accumulations of order amounts, add five order amounts in the Column field.

    2. Move the pointer over the order_amt field, click the 1icon on the right, and then choose Advanced Calculation-Date Cumulative-Month Cumulative.

      Refer to this operation to modify the cumulative calculation of order amount in the measure field to monthly cumulative, quarterly cumulative, current year cumulative and calendar year cumulative. image.png

    3. Click Update.

      image.png

    Dimensions containing date fields (month granularity)

    The following example uses a cross table to configure date rollup.

    1. On the Field tab of the dashboard edit page, add fields.

      • In the Dimensions list, double-click order_date (month). You can also drag this dimension to the Lines field.

      • In the measure list, find order_amt and double-click or drag this measure to the Columns section.

        Note

        To view the order amount and the order amount accumulated by quarter, current year, and calendar year, add four order amounts in the Column field.

    2. Move the pointer over the order_amt field, click the 1icon on the right, and then choose Advanced Calculation-Date Accumulation-Quarter Accumulation.

      Refer to this operation to modify the cumulative calculation of order amount in the measure field to quarterly cumulative, current year cumulative and calendar year cumulative. image.png

    3. Click Update. image.png

    Dimensions containing date fields (quarterly granularity)

    The following example uses a cross table to configure date rollup.

    1. On the Field tab of the dashboard edit page, add fields.

      • In the Dimensions list, double-click order_date (quarter). You can also drag this dimension to the Lines field.

      • In the measure list, find order_amt and double-click or drag this measure to the Columns section.

        Note

        To view the order amount and the current year cumulative amount and calendar year cumulative amount of the order amount, add three order amounts in the Column field.

    2. Move the pointer over Order_Amt, click the 1icon on the right, and then choose Advanced Calculation> Date Accumulation> Current Year Accumulation.

      Refer to this operation to modify the cumulative calculation of order amount in the measure field to current year cumulative and calendar year cumulative. image.png

    3. Click Update. image.png

    Dimensions containing date fields (year granularity)

    The following example uses a cross table to configure date rollup.

    1. On the Field tab of the dashboard edit page, add fields.

      • In the Dimensions list, double-click order_date (year). You can also drag this dimension to the Lines field.

      • In the measure list, find order_amt and double-click or drag this measure to the Columns section.

        Note

        To view the order amount and the cumulative order amount over the years, add two order amounts in the Column field.

    2. Move the pointer over the Order_Amt field, click the 1icon on the right, and then choose Advanced Calculation> Cumulative Date> Cumulative Year.

      image.png

    3. Click Update. image.png

    Multiple date fields in dimensions

    If a dimension contains multiple date fields, the system configures date accumulation with the smallest granularity date field.

    The following example uses a cross table to configure date rollup.

    1. On the Field tab of the dashboard edit page, add fields.

      • In the Dimensions list, double-click orderdate (year), orderdate (quarter), and orderdate (month). You can also drag these dimensions to the Lines field.

      • In the measure list, find order_amt and double-click or drag this measure to the Columns section.

        Note

        To view the order amount and the order amount accumulated by quarter, current year, and calendar year, add four order amounts in the Column field.

    2. Move the pointer over the order_amt field, click the 1icon on the right, and then choose Advanced Calculation> Date Accumulation> Custom. image.png

    3. In the Date Rollup dialog box, set the Date Field and Calculation Type tabs and click OK.

      You can select the year, quarter, month, week, or day granularity for a date field. The calculation type of a field varies based on the granularity. For more information about the mapping relationship, see Background information.

      In this example, the order date (month) is selected as the Date Field. 7

    4. Click Update.

    5. Add query conditions to a chart.

      This makes it easier for you to query the cumulative information within a specified time range.

      1. Follow the instructions in the following figure to add a query control. image.png

      2. On the Query Conditions page in the chart, configure the query conditions.

        For more information, see Create a query condition in a chart.

        Note

        When you configure a query widget, the associated field must be the same as the selected Date Field.

        image

    6. Enter query criteria to query the cumulative status of a month. image