Add a date filter to an analysis

Prerequisites

You have the Insights Reader license.
You have the Insights Author license.

Page location

Insights > Analyses > Click an analysis

You create filters on date fields by selecting the filter conditions and date values that you want to use. Comparisons are applied inclusive to the date specified.

EXAMPLE If you apply the filter Before 2/8/24, the records returned include all rows with date values through 2/8/24 23:59:59. If you don't want to include the date specified, clear the Include this date option. To omit a time range, use the Exclude the last N periods option to specify the number and type of time periods (minutes, days, and so on) to filter out.

You can also include or exclude nulls, or exclusively show rows that contain nulls in this field. If you pass in a null date parameter (one without a default value), it doesn't filter the data until you provide a value.

NOTE If a column or attribute has no time zone information, then Insights sets the default interpretation of that date-time data. For example, suppose that a column contains a timestamp, rather than a timestamptz, and you are in a different time zone than the data’s origin. In this case, Insights can render the timestamp differently than you expect. Insights and SPICE both use Universal Coordinated Time (UTC) times.

Procedures

Create a range filter for a date field

A range filter is a series of dates based on a time range and comparison type. You can filter records based on whether the date field value is before or after a specified date, or within a date range. You enter date values in the format MM/DD/YYYY. You can use the following comparison types:

Between – Between a start date and an end date
After – After a specified date
Before – Before a specified date
Equals – On a specified date

For each comparison type, you can alternatively choose a rolling date relative to a period or dataset value.

Create a new filter using a date field. For more information about creating filters, see Add filters to an analysis.
In the Filters pane, click the new filter. The Edit filter pane opens.
Select Date & time range from the Filter type drop-down list.
Select a comparison type from the Condition drop-down list: Between, After, Before, or Equals.

To use Between as a comparison, select the Start date and End date from the date picker controls that appear.

To include either or both the start and end dates in the range, select Include start date or Include end date.

To use an After, Before, or Equals condition, enter a date or click the Date field to open the date picker control and select a date instead. You can Include this date (the one you chose), Exclude the last N time periods, and select how to handle nulls.

To set a rolling date for your condition, click Set a rolling date. The Set a rolling date pane opens. Select Relative date and then select to set the date to Today, Yesterday, or you can specify the Filter condition (Start of or End of), Range (This, Previous, Next, Next N, or Last N), and Period (Year, Quarter, Month, Week, or Day). Click Save to return to the Edit filter pane.
(Optional) If you are filtering by using an existing parameter, instead of specific dates, select Use parameters, then select your parameter or parameters from the list. To use After, Before, or Equals conditions, select one date parameter. You can include this date in the range.

To use Between, enter both the start date and end date parameters separately. You can include the start date, the end date, or both in the range.

To use parameters in a filter, create them first. Usually, you create a parameter, add a control for it, and then add a filter for it. For more information, see Parameters in Insights.
If the Time granularity drop-down list is visible, select Day, Hour, Minute, or Second.
Click Apply.

Create a relative filter for a date field

A relative filter is a series of date and time elements based on the current date. You can filter records based on the current date and your selected unit of measure (UOM). Date filter units include years, quarters, months, weeks, days, hours, and minutes. You can exclude the current period and add support for Next N filters, similar to Last N with an added capability to allow for an Anchor date. You can use the following comparison types:

Previous – The previous UOM—for example, the previous year.
This – This UOM, which includes all dates and times that fall within the select UOM, even if they occur in the future.
To date or up to now – UOM to date, or UOM up to now. The phrase depends on the UOM that you choose. However, in all cases this option filters out data that is not between the beginning of the current UOM and the current moment.
Last n – The last specified number of the given UOM, which includes all of this UOM and all of the last n −1 UOM.

EXAMPLE Today is February 8, 2024. You use years as your UOM and set Last n years to 3. The filtered data includes data for all of 2024, plus all of 2023, and all of 2022. If you have any data for the future dates of the current year (2024 in this example), these records are included in your dataset.

Create a new filter using a date field. For more information about creating filters, see Add filters to an analysis.
In the Filters pane, click the new filter. The Edit filter pane opens.
Select Relative dates from the Filter type drop-down list.
Select a granularity of time that you want to filter by from the Time granularity drop-down list (Days, Hours, or Minutes).
Select a unit of time from the Period drop-down list (Years, Quarters, Months, Weeks, or Days).
Select how you want the filter to relate to the time frame from the Range drop-down list.

EXAMPLE You selected Months from the Period drop-down list. Your options in the Range drop-down list are Previous month, This month, Month to date, Last N months, and Next N months.

If you choose Last N or Next N, enter a number in the Number of field.

EXAMPLE Last 3 years, next 5 quarters, last 5 days.
(Optional) If you are filtering by using an existing parameter, instead of specific dates, select Use parameters, then select your parameter or parameters from the list.

To use parameters in a filter, create them first. Usually, you create a parameter, add a control for it, and then add a filter for it. For more information, see Parameters in Insights.
If the Null options drop-down list is visible, select Exclude nulls, Include nulls, or Nulls only.
In the Set Dates Relative To section, select one of the following options:
- Current date time – If you select this option, you can select Exclude last and then specify the number and type of time periods.
- Date and time from a parameter – If you select this option, select an existing datetime parameter.
Click Apply.

Create a top and bottom filter for a date field

A top and bottom filter is a number of date entries ranked by another field. You can show the top or bottom n for the type of date or time UOM you choose, based on values in another field.

EXAMPLE You can choose to show the top 5 sales days based on revenue.

Create a new filter using a date field. For more information about creating filters, see Add filters to an analysis.
In the Filters pane, click the new filter. The Edit filter pane opens.
Select Top and bottom filter from the Filter type drop-down list.
Select Top or Bottom.
(Optional) If you are filtering by using an existing parameter, instead of specific dates, select Use parameters, then select your parameter or parameters from the list.

To use a parameter for Top and bottom, select an integer parameter for the number of top or bottom items to show.

To use parameters in a filter, create them first. Usually, you create a parameter, add a control for it, and then add a filter for it. For more information, see Parameters in Insights.
Enter the number of top or bottom items you want to show in the Integer field and select a unit of time (Years, Quarters, Months, Weeks, Days, Hours, or Minutes).
In the By section, select a field to base the ranking on from the Select field drop-down list.
Select an aggregation from the Aggregation drop-down list.
(Optional) If the field for By has duplicates, add another field as a tie breaker. Click + Tie breaker, select another field, and select an Aggregation and Sort direction. To remove a tie breaker, click the trash can icon.
Click Apply.