Skip to main content

Use Flexible Date Filters to Control Time Range Display

Requirement Background

On the same page, only one date condition is needed, but the controlled cards need to display data across multiple time ranges, such as the most recent 7 days from the selected date, days 10 to 25 of the month containing the selected date, the most recent 12 months, and the most recent 3 years.

For example, if the selected date is 2019-10-17, the card time ranges to view are: 2019-10-11 to 2019-10-17, for 7 days in total; 2019-10-10 to 2019-10-25; 2018-11 to 2019-10, for 12 months in total; and 2017 to 2019, for 3 years of data. The visualization effect is shown below.

image.png

New Scenario 5: If the selected date is 2019-10-17, display daily sales amount from 2019-10-01 to 2019-10-17, and cumulative sales amount from 2019-10-01 to 2019-10-16, excluding 2019-10-17.

Scenario 1: Display Data for the Most Recent 7 Days from the Selected Date, with a Specified Date Range

Steps:

  1. Configure a date global parameter.

  2. Create one calculated field in the card to calculate the interval days between each row's date and the corresponding input parameter date. Use the difference value to freely control the range, as shown below.

image.png

  1. In the card filter, drag in the calculated field created in the previous step, and set the control range.

image.png

  1. Add an external time filter and set the linkage condition to link to the date global parameter used in the card. Alternatively, create a parameter filter and select the date global parameter used in the card.

image.png

Scenario 2: Display Data Within a Fixed Range of the Month Containing the Selected Date

Steps:

  1. Configure a date global parameter.

  2. Add three calculated fields in the card to control year, month, and day filtering, as shown below.

image.png

The principle is to calculate the difference between each row's date and the year, month, and day of the corresponding input parameter date, and then use the difference values to freely control the range.

  1. In the card filter, drag in the three calculated fields that control year, month, and day. Then set the control ranges. The day control is shown below, while year and month are both set to equal 0.

image.png

  1. Add an external time filter and set the linkage condition to link to the date global parameter used in the card. Alternatively, create a parameter filter and select the date global parameter used in the card.

Scenario 3: Display Data for the Most Recent 12 Months from the Selected Date

Steps:

  1. Configure a date global parameter.

  2. Add two calculated fields in the card to extract the year and month from the input date parameter:

  • Parameter Month: month([DYNAMIC_PARAMS.Date])
  • Parameter Year: year([DYNAMIC_PARAMS.Date])

image.png

  1. This step is important. The principle is to control the filter range by using Start YearMonth <= Dataset YearMonth <= End YearMonth. The control logic is to subtract one from the other: data where Start YearMonth - Dataset YearMonth <= 0 is within the required display range. Dataset YearMonth - End YearMonth works similarly. Here, End YearMonth is the parameter year and month.

Operation: preprocess the dataset year and month for control calculation by creating the following three calculated fields:

  • Dataset YearMonth: year([Date Field]) * 100 + month([Date Field])
  • Start YearMonth: case when [Parameter Month] = 12 then [Parameter Year] * 100 + 1 when [Parameter Month] < 12 then ([Parameter Year] - 1) * 100 + [Parameter Month] + 1 end. Logic: if the parameter month is December, take January of the current year; if it is not December, take the selected month of the previous year + 1.
  • End YearMonth: [Parameter Year] * 100 + [Parameter Month]

image.png

Numeric calculation is used instead of directly concatenating year and month with concat(), because a single-digit month such as January becomes 20191 instead of 201901 after concatenation, causing calculation errors when compared with double-digit months such as October, 201910. Therefore, this numeric processing is required.

  1. Add two fields to calculate the differences for year and month:
  • Start Control: [Start YearMonth] - [Dataset YearMonth]
  • End Control: [Dataset YearMonth] - [End YearMonth]

image.png

  1. Place the two fields from Step 4 into the card filter, and set both control ranges to <= 0.

image.png

  1. Add an external time filter and set the linkage condition to link to the date global parameter used in the card. Alternatively, create a parameter filter and select the date global parameter used in the card.
Description

The formulas above are implemented based on extracted datasets, meaning Spark functions. If you use a direct dataset, replace them with the corresponding database function syntax.

For example, if you create calculated fields directly in an Oracle direct dataset, the card reports the error ORA-00904: "YEAR": invalid identifier.

The reason is that Oracle does not support using the year and month functions directly to obtain the year and month from dates. You need to change them to EXTRACT(MONTH FROM TO_DATE([DYNAMIC_PARAMS.Date], 'DD')) and EXTRACT(MONTH FROM TO_DATE([DYNAMIC_PARAMS.Date], 'DD')).

Scenario 4: Display Data for the Most Recent 3 Years from the Selected Date

Steps:

Create fields by following the steps in Scenario 3 exactly, and adjust the following fields as needed:

  • Start YearMonth: ([Parameter Year] - 2) * 100 + 1
  • End YearMonth: [Parameter Year] * 100 + [Parameter Month], meaning up to the year and month containing the parameter.

[Parameter Year] * 100 + 12, meaning the full year containing the parameter.

image.png

Scenario 5: Display Sales Amount on the Selected Date and Cumulative Sales Amount from Month Start to Yesterday

Steps:

1. Configure a date global parameter.
2. Add three calculated fields in the card to control year, month, and day filtering, as shown below.

image.png

The principle is to calculate the difference between each row's date and the year, month, and day of the corresponding input parameter date, and then use the difference values to freely control the range.
3. In the card filter, drag in the three calculated fields that control year, month, and day. Then set the control ranges. Set day control to greater than or equal to 0, as shown below, and set year control and month control to equal 0. This filters only data from the 1st day of the current month to the selected date, without displaying data after the selected date.

image.png

4. Create a field named Cumulative Amount from Month Start to Yesterday and use a combined function to calculate the cumulative amount from the start of the month to yesterday.

The conditional function case when DAYOFMONTH([payment_date]) between 1 and DAYOFMONTH([DYNAMIC_PARAMS.Date]) - 1 then [payment_price] else 0 end can filter daily sales details from the start of the month to yesterday. The window function sum( ) over(partition by month([payment_date]) order by [payment_date]) can calculate the cumulative value for daily sales details from the start of the month to yesterday. If you need to group by more dimensions, place dimension fields after partition by in order. For details about cumulative calculation, see Calculate Monthly Cumulative Sales Amount.

image.png

5. Drag the date field into the dimension area, drag the Sales Amount field from the dataset into the value area, select Sum as the aggregation method, and rename it to Sales Amount on Selected Date. Then drag the Cumulative Amount from Month Start to Yesterday field created in the previous step into the value area, and set the aggregation method to No Processing.

If the date field is not placed in the dimension area, the Sales Amount on Selected Date field with Sum actually calculates cumulative sales amount from the start of the month to the selected date, rather than sales amount on the selected date. In this case, create a field case when [payment_date] = [DYNAMIC_PARAMS.Date] then [payment_price] else 0 end and drag it into the value area to calculate sales amount on the selected date. Set the aggregation method to Sum. The comparison of the two methods is shown below.

image.png

6. Add an external time filter and set the linkage condition to link to the date global parameter used in the card. Alternatively, create a parameter filter and select the date global parameter used in the card.

Description

All calculated fields above use built-in Spark SQL functions. If the card uses a direct dataset, use the corresponding database functions.

Case

Demo Experience

Log in to experience the demo case: Custom Quick Date Range.

Case Download

Download this case from Guandata App Marketplace to your local environment: Dynamic Time Macro.