Filter Rows Pipeline Step Examples

Data Pipeline

The filter rows data pipeline step can quickly filter your data using multiple columns and methods. The examples below demonstrate the flexibility of this tool to help you visualize the data you need to see to make informed decisions.

Comparing Columns

If you only want to return rows where one column has a certain relationship (greater than, less than, etc.) to another column, you can use the Filter Rows step to compare these columns.

In the example below, we want to return only rows where our monthly Adwords sales are higher than our monthly Print sales. Here’s the chart we’re working with:

Compare columns in filter Pipeline step

We’ll add a Filter Rows step in the Pipeline (+Add Transformation > Filter Rows) with the following settings:

Filter Rows in the Pipeline

If you’d like to set multiple rules, you can do this as well by selecting a new column from the New Rule dropdown. When you’ve completed adding your filter rules, click Apply & Close.

Your chart results will now automatically be filtered depending on the selected columns’ results

Compare filter Pipeline steps 2

Filtering Specific Days of the Week

Say you want to filter out weekends or your business days comprise of a Monday - Saturday week or a Monday - Thursday week. How do we filter these other days of the week out?

This can be done in our Data Pipeline. Our Data Pipeline uses SQLite so we can employ functions available in SQLite. Our first step is our query. We will need to add our date column as a Dimension in our chart and select “Day of” as our time bucket for the date field. Let’s use this query as an example:

Create a chart to filter by days of the week

Our next step will be to add an Add Column step in the Pipeline. We will call this column “DOW” and select to use a Custom formula from the Formula Type dropdown menu. We’ll then add the following function in the Formula field, replacing Day of Created Date with your column name:

strftime('%w', "Day of Created Date")

Add a column in the Pipeline

Once we apply this Pipeline Step, we’ll have our days of the week listed out with Sunday = 0 and Saturday = 6. We can now filter out values 0 and 6 using a Filter Rows step. We’ll exclude rows matching ALL conditions where DOW is one of 0 and 6 as done below:

Filter rows in the Pipeline

And that’s it! We’ve now excluded all the Saturday and Sunday data. You can now hide this column using a Hide Columns Pipeline step if it’s no longer needed.

Filter Variable Results

Our Filter Rows step can also allow custom inputs. This includes Relative Date Variables and Dashboard Variables which allows you to use data outside of your chart results to filter your chart output.

Variables can be used as is in the input field or can be combined to further customize your filters.

For example, if you want to only include results in your chart for dates later than your Calendar variable’s start date (here, named CALENDAR), you can select Custom Input instead of an existing column in the Filter Rows step and enter the variable name between curly brackets. You will then append START after the variable name to highlight you’d like the start date only. For example:

{CALENDAR.START}

If your variable is set to only one date, you won’t need to append anything to the variables name and can input it as is.

Filter Rows using Dashboard Variables

Below is an example of the Filter Rows step, this time using our Relative Date variable {TODAY} to only include results from dates before today’s date. This date will automatically change according to the current day and filter the chart without needing changes.

Filter Rows using Relative Date Variables


Related Data Pipeline Help Articles

See more