Assistant filtering

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 only supported in 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 set 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 set 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 set 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 set 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.

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.

You can also filter for certain holidays (such as Christmas, Christmas eve, New Year’s day, Labor day, Valentine’s day, and more). Example, show me the total income between Halloween and Christmas.

Here are more ways to use time entity filters:

  • Show sales before last Thursday
  • Show sales in 2021
  • 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 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 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, YTD, and QTD
    • 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.