Filters in the Assistant

You can ask the Assistant to apply filters to a data set by using words and associated synonyms.

The Assistant can apply filters to categories (such as Product column), geographical strings (such as Country or State), and temporal expressions (such as Month or Year).

When you ask the Assistant to apply a filter to a specific column, you are applying an expression filter.

Note: Generally, the Assistant applies filters before any aggregation is applied to the data.

The following are some examples of expression filters:
  • show the top 3 products by sales
  • show sales where month is January
  • show products by sales where inventory > 500
  • show products by sales for California
  • show top <num> <column1> by <column2>
    • Displays the top values from <column1> based on the context of <column2>. For example, show top 5 Sales by Region. If <num> is not specified, a default value of 10 is used.
    • <column1> is an aggregated or non-aggregated measure, while <column2> is a categorical column.

You can use the following operators and modifiers to help you filter:

  • in
  • for
  • if
  • where
  • with
  • including
  • excluding
  • top percentage
  • bottom percentage
  • greater than
  • less than
  • top (post data aggregation)
  • bottom (post data aggregation)
Note:

  • If you apply top or bottom filter to a categorical column, you must supply a measure.

  • top and bottom filters are applied to the data after any data aggregations.

  • Measures support greater than, less than, greater than or equal to, less than or equal to, equal to, and not equal to type filters.

Time entity filters

Time entity filters are temporal expressions. The conversational Assistant understands references to date, time, month, year, seasons, and holidays.

Using these temporal expressions lets you drill down and create more focused visualizations.

Note: Filtering with time entity values is supported in only English.

For the Assistant to apply time entity filters, the data set must have a Timestamp or Date/Time column. The Assistant matches time entity values to the data source in the following order:

  1. Timestamp column
  2. Date/Time column
  3. Any other column that best matches the filter

While the Assistant understands reference to time even if the data source does not include a column with values that match the requested time value, the data set must have a Timestamp or Date/Time column. If these columns are not present in the data set, the Assistant applies the filter to any column that best matches the requested filter.

For example, ask the Assistant to show sales for 2021. If the data source has a Timestamp column, the Assistant applies the filter to the column and returns data for 2021-01-01 00:00:00 to 2021-12-31 23:59:59.999.

If the data source does not have a Timestamp or Date/Time column, the Assistant matches 2021 to the Year column.


Using time entity filters

The Assistant can filter based on relative time references (this, that, last, and next). For example, show sales this Thursday or show sales from last Monday to this Monday.

It is important to note that:

  • this returns a value for the day, month, week, or holiday that is in the current calendar year

  • next returns a value for the next occurrence of day, week, month, or holiday in the future

  • last returns a value for the occurrence of the day, week, month, or holiday that occurred most recently

For example, assuming that the current month and year is August 2022:

  • show sales for this July returns results for July 2022

  • show sales for next July returns results for July 2023

  • show sales for last July returns results for July 2022

When you prefix this with a day of the week, the Assistant returns the day of the current week, with Sunday being the start. For example, if today is Monday, this Sunday returns the Sunday that just passed. Last Sunday also returns the Sunday that just passed.

You can also use open time intervals that have no start or end date. Example, show sales after January 2021.

Or, ask the Assistant to use closed time intervals to filter. A closed time interval has a start and end date. Example, show laptop sales for January 2021 or show sales from January 2021 to March 2021.

Using time entity to filter

Filter applied to data set

When you filter with time, the Assistant understands time relative to the time that is in your Cognos Analytics configuration. If your current time is 3:15 PM, show sales in the previous hour returns data from 2:00 PM to 2:59 PM for the same day.


The Assistant supports date references such as YTD, QTD, MTD, and WTD.

  • YTD shows results for year to date starting from Jan 1, 00:00 to current date and time.

  • QTD shows results for quarter to date by looking at the closest start of quarter month to today. QTD is defined as:

    • Jan 1, 00:00 to Mar 31, 23:59

    • April 1, 00:00 to June 30, 23:59

    • July 1, 00:00 to Sept 30, 23:59

    • Oct 1, 00:00 to Dec 31, 23:59

  • MTD shows results for month to date starting from <month> 1st, 00:00 to current date and time.

  • WTD shows results for week to date starting from Sunday to current date and time.

You can also filter for U.S. holidays or special days (such as Christmas, Christmas eve, New Year’s day, Labor day, Valentine’s day, and more). Example, show me the total income between last Halloween and this Christmas.

Here is a list of U.S. holidays and special days that the Assistant supports:

  • Christmas and Christmas Eve

  • New Year’s Eve and New Year’s Day

  • Valentine’s Day

  • MLK Day

  • Memorial Day and Memorial Day weekend

  • Independence Day

  • Labor Day and Labor Day weekend

  • Father’s Day

  • Mother’s Day

  • Halloween Day

  • Thanksgiving Day

  • Black Friday

Additionally, the Assistant recognizes certain typos and abbreviations. For example, it recognizes:

  • Xmas as Christmas

  • Jan as January

  • Thu, Thur, and Thurs as Thursday

  • Vanlentine Day as Valentine’s Day


Examples

Here are more ways to use time entity filters:

  • Show sales before last Thursday

  • Show sales in 2021

  • Show sales from last Monday to this Monday

  • Show sales between March and Mother’s day

  • Show profit for December 31, 2021 at 10 pm

  • What is our expected revenue on the day before Labor Day?

  • Show me sales for camping equipment from June 1-5

  • Show me total sales last year

  • Show the average sales for past 3 weeks

  • Show the top 3 products sold on the third Monday after last Christmas

  • show sales after next Christmas

  • Show October 2020 sales by product

  • Show sales after October 12, 2020

  • Show me sales up to a week ago

  • Show sales between last Monday and last Wednesday

  • Show sales every January and March

  • Show sales every January and every March

  • Show sales every single August

  • Show revenue in Canada for every Friday

  • Show sales each August and September and each Monday

  • Show sales by product each and every August

  • Show sales where transaction date is not January

  • Show sales where transaction date is not before January

  • Show sales where transaction date is not between January and March

You can combine expression filters with time entity filters. For example, show sales by product where date/time is January or show sales where product is coffee.


Limitations

The following limitations currently apply to time entities:

  • The Assistant always returns data for the closest day in the future.

  • Season recognition is based on time periods in the northern hemisphere.

  • Every is not supported on time entity filters that match to the Timestamp or Date/Time columns.

    It is only supported if another column exists in the data set that matches the text of the requested filter. For example, show sales every January returns data only if the data set has a column where the filter “January” can be applied.

  • The global parameter _as_of_date is not supported.

  • The following time-based filters are not supported:

    • References to time zones (ex. show sales for October 1 at 2 pm GMT)
    • Fractional periods (ex. half day or quarter month)
    • 2nd Tuesday
    • Weekly or monthly
    • Month over month
    • Time periods such as Q1 and Q2 are not supported. Instead, ask for first quarter of, 2nd quarter of, this quarter, or last quarter.

  • Durations such as two days and three hours or two and a half days are not supported. You can filter using simple durations such as two days.

  • Negation is not supported on time entity filters that match to Timestamp or Date/Time columns.

    For example, show sales not in June is not supported.

    However, show sales where date/time is not January and show sales not in Canada but in Brazil are supported.

  • You cannot combine sequential or non-sequential time entities in a single sentence. For example, show sales in February, March and April is not supported.

  • For phrases such as sales where date is not April 2013, the filter is applied as expected but the visualization does not display any data. This is because sales that are both after 11:59 PM on April 30, 2013 and before 12:00 AM on April 1, 2013 cannot be displayed.

  • Sunday is recognized as the start of the week and not Monday.

  • Only the American English spelling for Labor Day is supported.

  • Only U.S. holidays are supported.

  • You cannot mix holidays or special days with months or days of the week when you use prefixes this, last, or next. For example, the Assistant does not support the use of show sales from last Tuesday to next Christmas Day.