All Products
Search
Document Center

Quick BI:Query Data based on a Date Field

Last Updated:Jan 20, 2025

This topic explains how to configure a filter bar to query data based on a date field.

Prerequisites

  • You have created a dashboard, and the dashboard chart includes a date field.

  • You have added an empty filter bar to the dashboard. For more information, see Create a Filter Bar.

Procedure

You can configure the date filter bar as shown in the figure below.

image

Basic configuration

Display type

The date filter bar supports date selection.

Time granularity

  • The time granularity matches that of the associated date field. For instance, if the associated date field is a year field, the time granularity is year. If it's a month field, the granularity is year-month. If it's a day field, the granularity is year-month-day.

  • For a YYYYMMDD HH:MM:SS date field, you can switch time granularities. For details, see the Date Granularity Switching section of this topic.

Data source

The filter bar's data source can be either the Default Date Picker or a Dataset Field. The default date picker supports date cascade, while the dataset field option supports same-source cascade, non-same-source cascade, and date cascade.

  • Default Date Picker

    There is no data source. It only supports date cascade, not cascading with other controls.

    image

  • Dataset Field

    Select a dataset as the data source. This supports date cascade along with cascading with dropdown and tree controls.

    image

Set Time Filter Range

By setting the time filter range, you control the volume of data filtered for the query. Once set, you cannot select times outside this range.

image

    • The default Interval Type is None. It also supports Start at, End at, and Time interval options.

      image

    • For example, using Start at as the Interval Type, you can set the Relative Time range as shown below.

      image

      Note

      The granularity selection of T allows dropdown selection of all granularities included in the current time granularity. For instance, if the current granularity is year-month-day, you can switch to year, month, or day. If it's year-month-day-hour-minute-second, you can select down to the second.

      Applicable scenarios for this feature include instances where the time granularity is YYYYMMDD HH:MM:SS, and there's a need to switch the relative time in seconds. If the granularity is solely in seconds, determining the number of seconds required can be tedious. To simplify, you can switch to daily granularity for a swift overview of time data across various dates.

    • Here's an example of setting the Exact Time range using Start at as the Interval Type.

      image

    • Selecting Dynamic Query Time Window and setting the time amount for Maximum Query Per Time limits the maximum query time within the set range.

      image

Filter method

Supports filtering by single day or day interval. For configuration details and effects, refer to the Single Date Query and Date Interval Query sections of this topic.

Set Default Value

You can establish the default value using either Manual setting or Dynamic function.

  • You can manually set the default value for relative or exact time.

    image

  • If data for the current or previous day isn't generated, you can't accurately select a date using relative or absolute time. Instead, use dynamic functions to retrieve the date when data is generated.

    image

Enable Quick Date Switching

Once enabled, a quick switch is added to the date filter box on the report page.

Note

If the time granularity is set to year, quarter, month, week, or day and the Screening method is singleday, you can use the Switch Dates feature.

When Filter Method is set to Single Day and Enable Quick Date Switching is on, a quick arrow is added to the date control on the dashboard for fast date switching, as shown below.

箭头切换

    Note

    The quick arrow is operational only when a specific date is present in the date control box.

    If a time filter range is set, you cannot switch to a date outside this range using the arrows.

Single date query

The example below demonstrates querying sales data for a specific day using the shipping_date(day) field.

  1. On the Dashboards page, select a dashboard. The chart and filter bar displayed on the dashboard are shown in the figure below.

    image

  2. Click Advanced Configuration on the filter bar.

  3. On the Query Condition Settings page, set the query conditions as depicted below.

    image

  4. After setting up, select a date in the query conditions and click Query to view the sales data for that day.

    1经验

Date interval query

This example shows how to query the profit of a store over a specific time period using the shipping_date(day) field.

  1. On the Dashboards page, select a dashboard. The chart and filter bar displayed on the dashboard are shown in the figure below.

    The dashboard chart must include a Date field.

    image

  2. Click Advanced Configuration on the filter bar.

  3. On the Query Condition Settings page, set the query conditions as depicted below.

    image

  4. After setting up, select a date range in the query conditions and click Query to view the store's profit for that period.

    image

    If the Screening method is set to dayInterval, you can configure parameters for efficient data viewing.

    • Interval Type supports Start at, End at, Time interval, and Quick interval.

      Start at

      image

      End at

      image

      Time interval

      imageQuick interval

      image

    • Customize Quick Interval: Allows for multiple interval scenarios, enabling you to customize the quick interval value.

      image

      Note
      • This option is configurable only when Interval Type is set to Quick Interval.

      • It supports configuring quick intervals based on the previous day.

        image

    • Set Charts Associated With Only Start And End Times: In the dashboard, you can Set Charts Associated With Only Start And End Times to apply the same date interval to different charts.

      After selecting this option, you can specify one of the following conditions for chart association:

      • Limit start time

        image

      • Limit end time

        image

      • Limit time interval

        image

      Note

      This option is configurable only when Interval Type is set to Time Interval.

Date granularity switching

Select a time granularity that suits your business needs to view data at different levels of detail on a dashboard. This allows you to configure a single filter bar instead of using multiple dashboards or tabs for various granularities.

Note

To change time granularity, ensure the associated date field is at the YYYYMMDD HH:MM:SS level, such as shipping_date.

  1. On the Query Condition Settings page, set the query conditions as shown below.

    In this example, the date field is shipping_date(ymdhms).

    image

    In the Basic Configuration area, the parameter configuration is as follows:

    • Time Granularity: After selecting the time granularity field for the associated field, a default value is automatically filled in the Time Granularity.

      If the associated field is shipping_date, the default granularity is YYYYMMDD HH:MM:SS. You can also select other granularities such as years, Year-quarter, Year-week, and year month day from the dropdown list based on your needs. Unwanted granularities can be removed.

    • Sub-granularity Settings: These settings allow you to quickly view data from different time perspectives on a dashboard.

      Note

      Sub-granularity configuration is necessary only when Time Granularity includes two or more values.

      image

    • Set Default Filter Value: Assign a default value for the filter bar.

      With Sub-granularity Settings in place, you can set different default values for each granularity, facilitating quick data queries when switching between them.默认值_4.3.1

  2. After configuring, switch the granularity in the query conditions, select a date, and click Query.

    切换时间_4.3.1

    You can also switch sub-granularities to quickly query data.切换粒度_4.3.1

    Note

    If the dataset includes a Fiscal Year configuration, the Fiscal Year Starts At setting in the filter bar follows the dataset's field configuration and cannot be altered. For more details on fiscal year settings, see Create and Manage Datasets.