1. Visualizing Data and Building Reports in Oracle Analytics Cloud
  2. Visualize Data
  3. Filter Your Data
  4. Apply Different Filter Types

Apply Different Filter Types

Different filter types enable you to focus in on the data that you're interested in.

Topics:

Apply Range Filters

You use Range filters for data elements that are numeric data types and have an aggregation rule set to something other than none.

Range filters are applied only to measure columns and limits data to a range of contiguous values, such as revenue of $100,000 to $500,000. Alternatively, you can create a range filter that excludes (as opposed to includes) a contiguous range of values. Such exclusive filters limit data to two noncontiguous ranges (for example, revenue of less than $100,000 or greater than $500,000).
If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, hover over a workbook, click Actions (Actions menu ellipsis icon), then select Open.
  2. In the Visualize canvas, click the filter, then click Range.
    Description of filter-menu.png follows
    Description of the illustration filter-menu.png

  3. Click By to view the selected list of Attributes, then configure the filter:
    • Click a member to remove or add it to the selected list.
    • Click the Plus (+) icon to add a new member to the selected list.
    • Set the range that you want to filter on by moving the Min and Max sliders in the histogram.
  4. Click outside of the filter to close the filter panel.

Apply Top Bottom N Filters

Use the Top Bottom N filter to filter a measure or attribute and display its highest or lowest values.

  1. To apply the Top Bottom N filter to the canvas and all visualizations in the workbook:
    1. On the Home page, hover over a workbook, click Actions (Actions menu ellipsis icon), and then select Open.
    2. In the Data panel of the visualization, select the attribute or measure that you want to filter on and drag and drop it to the filter bar.
    3. In the filter bar, click the filter, then click Top Bottom N. You can only convert a range filter to Top Bottom N filter.
  2. To apply the Top Bottom N filter to a specific visualization in the workbook:
    1. In the canvas, select the visualization that you want to filter.
    2. In the Data Panel, locate the attribute or measure that you want to filter on and drag and drop it to the Filter drop target in the Grammar Panel.
    3. In the filter bar, click the filter, then click Top Bottom N.
  3. To apply the Top Bottom N filter to an on-canvas filter:
    1. With the canvas selected, go to the Data Panel and click Visualizations, then click the List Box filter.
    2. In the Data Panel, locate the attribute or measure that you want to filter on, and drag and drop it to the List Box visualization that you just created.
  4. To configure a Top Bottom N filter, click the filter, then:
    • To change between top to bottom, click Method value and click Top or Bottom.
    • To specify the number of rows that are displayed, click the Count field and enter the number of rows.
    • To change which attribute or measure column to limit by, click the By field and select an attribute or measure or custom calculation included on the canvas. Or click Plus (+) to locate and select the attribute or measure or metric that you want to limit by
  5. Click outside of the filter to close the filter panel.

Apply List Filters

You apply list filters to text, non-aggregatable numbers, and dates, and you can choose which members to include or exclude from the filter.

If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, hover over a workbook, click Actions (Actions menu ellipsis icon), then select Open.
  2. In the Visualize canvas, select a filter in the filter bar or select an on-canvas filter, then click List.
  3. Locate the member you want to include and click it to add it to the Selections list. Alternatively, use the Search field and the Search Options Menu to find a member you want to add to the filter. Use the wildcards * and ? for searching.
  4. Optional: Perform the following actions related to the Selections list:

    • Click a member to remove it from the list.

    • Click the eye icon next to a member to filter it out, but not remove it from the list.

    • Click Menu at the top, and select Exclude Selections to exclude members from the list.

    • Click Null to include members with null values in the list.

    • Click Add to add all members to the list.

    • Click Clear to remove all members from the list.

  5. Click outside of the filter to close the filter panel.

Apply Date Range Filters

Date range filters use calendar controls to adjust time or date selections. You can select a single contiguous range of dates, or use a date range filter to exclude dates within the specified range.

If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, hover over a workbook, click Actions (Actions menu ellipsis icon), then select Open.
  2. In the Visualize canvas, select a filter in the filter bar or on-canvas filter, and click Date Range.
  3. Click the drop-down list, and select the type of date range you want to apply (for example, Range, Start at, End at, Equal).
  4. Use the date pickers to configure the range.
  5. Click outside of the filter to close the filter panel.

Apply Relative Time Filters

Use the relative time filter on a Date or Date/Time column to display data for a specified time period based upon the current date and time.

You can specify a relative time period as either an explicit number of past or future time units (for example 2 years), or you can specify a previous period. For example, Year To Date which includes data from 1-January this year to the current date, and Month To Date which includes data from the beginning of the month to the current date.

You can apply a Relative Time filter only to date columns that already exist in the data source, and not to derived columns such as Year, or Quarter. The Relative Time filter type supports Date (with no time of day portion) and DateTime (that is, TIMESTAMP with both date and time of day) column types.

The current date and time used in queries is the Oracle Analytics server host's date and time in the server's timezone (not the browser host's time or timezone).

If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, hover over a workbook, click Actions (Actions menu ellipsis icon), then select Open.
  2. In the Visualize canvas, click the filter in the filter bar or canvas, then click Relative Time.
  3. Select a Type that defines the range that you want to filter.
    • Last - You specify a Period to apply to the selected Time Level (Years, Quarters, Months, Weeks, Days, and includes Hours, Minutes, and Seconds if the column time is TIMESTAMP) relative to today's date, to display records for the date values for that period.

      Last filters that are based on a DateTime column and which have a grain of Day or longer (for example, Year, Quarter, Month, Week, Day), retrieve data from the same time of day on the starting day. For example, if the server date/time is currently Thursday 3:15pm , a Last 2 Days filter on a DateTime column retrieves data with timestamps between Tuesday 3:15pm and Thursday 3:15pm in the server's timezone. Filter queries that are based on a DATE column type (which by definition have no time of day associated) only depend on the server host's date, not the time of day.

    • Next - You specify a future Period number to apply to the selected Time Level (Years, Quarters, Months, Weeks, Days, also Hours, Minutes, and Seconds if the column time is TIMESTAMP) relative to today's date, to display records for the date values for that period.
    • To Date - You specify a past Time Level (Year, Quarter, Month, Week, Day, also includes Hour, and Minute if the column time is TIMESTAMP) relative to today's date, that you want to apply to the date values in the visualization.

      A To Date filter retrieves data from the beginning of the chosen period, for example, Month to Date retrieves data from midnight of the first day of this month up until the current date and time (that is, Today or Now).

  4. Click outside of the filter to close the filter panel.

Filter Data Using an Expression Filter

Using expression filters, you can define more complex filters using SQL expressions. Expression filters can reference zero or more data elements.

For example, you can create the expression filter "Sample Sales"."Base Facts"."Revenue" < "Sample Sales"."Base Facts"."Target Revenue". After you apply the filter, you see the items that didn’t achieve their target revenue.

You create expressions using the Expression Filter panel. You can drag and drop data elements to the Expression Filter panel and then choose functions to apply. Expressions are validated for you before you apply them.

If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, hover over a workbook, click Actions (Actions menu ellipsis icon), then select Open.
  2. In the Visualize canvas, go to the filter bar and click Add Filter and then select Add Expression Filter.
  3. In the Expression Filter panel, compose an expression in the Expression field. For example, enter "Sample Sales"."Base Facts"."Revenue" < "Sample Sales"."Base Facts"."Target Revenue" to focus on items that didn’t achieve their target revenue.
  4. In the Label field, give the expression a name.
  5. Optional: Enter a description.

    The description displays in the tooltip when you hover over a calculation.

  6. Click Validate to check if the syntax is correct.
  7. When the expression filter is valid, then click Apply. The expression applies to the visualizations on the canvas.