Quick BI:Trend Analysis Table

Prerequisites

• A data model is created, and a dataset that contains a day-granularity date field such as report_date(day) is used for data modeling. For more information, see Create a dataset.

• A dashboard is created. For more information, see Create a dashboard.

Limits

Take note of the following limits when you add data to a trend analysis table:

• You can specify at least one measure for Measure/Column.

• You can specify only one dimension for Date/Column. The dimension must be a date field of the day granularity.

Overview

Scenarios

Commonly used in the analysis of macro indicators, you can analyze annual, quarterly, monthly, weekly, fiscal year and nearly 7 days of data, and can further compare individual indicators, trend, mean, normalized analysis.

Renderings

Configure a trend analysis table

1. On the Data tab, select dimensions and measures based on your business requirements.

   • In the Measures list, double-click Order Amt, Profit Amt, Shipping Cost, Unit Price, and Discount Point. You can also drag these measures to the Mea. /Line field.

   • In the Dimension list, double-click the report_date(day) dimension. You can also drag this dimension to the Date (Dimensions) field.

     Note

     You can add only a date field of the day granularity to the Date (Dimensions) field.

2. Click Update to create the LBS heat map.

   

Configure a trend analysis fact table

• On the Fields tab, click the icon next to Measure /Column and follow the instructions in the following figure to configure the tree style of the trend analysis table.

  

• On the Style tab, configure the style of the trend analysis fact table. For more information about how to configure a common chart style, see Configure the title area of a chart.

  You can enter a keyword in the search box at the top of the configuration section to search for configuration items. You can also click Expand /Collapse All Categories in the right-side chart.

  

  • In the Basic Table Style section, configure the basic item style of the table.

    

    Configuration item	Description
    Custom Table Theme	The following themes are supported: Zebra, Wireframe, Simplified, and Minimal.
    Adjust the width of the first column	Adjust the width of the first column: You can configure the width of the first column for the trend analysis fact table. Adaptive by Container: The system displays the column width based on the field value. Custom: Specify the display width of the field value. Unit: pixels. Default value: 160.

  • In the Calculation Method section, configure the calculation method for the trend analysis table.

    

    Configuration item

    Description

    Method

    You can set the calculation method of the trend analysis table. The calculation method can be Average /Cumulative or regular Calculation. By default, Average /Cumulative is selected. If you click Average /Cumulative, the related settings are configured. Switching the regular calculation is not required. Note regular calculation: The Quick BI performs aggregation calculation within the date range of week, month, quarter, year, or custom date granularity based on the aggregation method configured for the metric. For example, within the month range, the calculation method is month cumulative numerator /month cumulative denominator. This method is suitable for scenarios where data changes within a certain time range are analyzed. If you select Turn on Average /Cumulative, Average and Cumulative are displayed in the upper-right corner of the chart. Select the Custom Average /Cumulative Switch check box to manually set the Average /Cumulative calculation method for each measure.

  • In the Header section, configure the style of the row header in the crosstab.

    

    Parameter	configuration items Content	Description
    List Header	Background Color	Sets the fill color of the list header background.
    	Text	Sets the list header text style.
    Row Header	Background Color	Sets the row header background fill color.
    	Text	Sets the row header text style.

  • In the Filter section, set Time Period and Default End Date.

    

    Configuration item	Description
    Time cycle switching	You can select Year, Quarter, Month, Week, Day, or fiscal year. You can also select a custom time granularity. Note 1. fiscal year Cycle Start Month is supported. Valid values: January to December. By default, April is selected. 2. Week range definition: When the calculation method is regular calculation, you need to download the function script and run it on the corresponding data source to customize the week range. You can customize the start time of a week for the following data sources: MaxCompute, MySQL, Oracle, SQL Server, PostgreSQL, ClickHouse, Alibaba PolarDB for MySQL, Presto, Hive, and Apache Doris.
    Default Deadline	Set the Display Date Filter parameter and the default value. If you select Show Date Filter, End Date is added to the upper-right corner of the chart. The default value is set to T- X: the X can only take positive integers. For example, if the X value is 1 and the current time is 2021-10-25, 2021-10-24 is displayed. You can set the Defaults parameter to Custom date. If this parameter is set to Custom date, you can configure a custom expiration date.
    Time period is selected by default	You can set the Default Time Period parameter. The default value is the first item on the left.

  • In the Auxiliary Display section, you can set the Display Mode and ScrollBar Threshold parameters.

    • If you set Display Mode to Slide into Display, the scroll bar is displayed only when you place the pointer over the table.

    • If you want to keep the scroll bar on, you can select Resident Display.

      

    • If you want to make the scroll bar more visible, you can adjust the thickness of the scroll bar.

      

• On the Analysis tab of the Chart Design page, click the Advanced Settings tab and configure data comparison for trend analysis.

  If you select Enable Table Data Comparison, Year-on-Year and Month-on-Month are displayed in the upper-right corner of the chart.

  

  • Display Type: You can select Year-over-Year or Month-over-Month.

    Note

    The date fields that are used in the trend analysis fact table are of the day granularity. Therefore, you can configure the Year-on-year and Chain ratio parameters for data of the day, week, month, quarter, and year granularities, and custom periods. You can compare data based on your business requirements.

  • Show: This parameter can be set to Percent Variance or Variance.

    • Variance: The value of this parameter is calculated by using the following formula: Variance = Data in the current period - Data in the previous period.

    • Percent Variance: The value of this parameter is calculated by using the following formula: Percent Variance = [(Data in the current period - Data in the previous period)/Data in the previous period] × 100%.

    Take the previous period data (the week interval is 2015-9) and the current period data (the week interval is 2015-10) as an example to view the variance value and variance percentage of orders in this interval.

    • From the following figure, you can see that if the data in the current period is 6113 and the data in the previous period is 6577, the difference value is -464( -464=6113-6577 ).

      

    • From the figure below, you can see that the data in the current period is 6231 and the data in the previous period is 8118, then the difference percentage is -7.06% (-7.06%=[(6113-6577)/6577]*100% ).

      

• Display Style: Configure the display color of positive and negative numbers.

  

Configure a trend analysis chart

The following figure shows how to view the trend analysis chart of a metric. You can click the trend icon to the right of the name of a metric on the trend analysis fact table to view the trend analysis chart of the metric.

You can configure a trend analysis chart on the Analysis tab of the Chart Design page.