Filter Rows examples
The Filter Rows 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 (e.g., 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 number of Canceled documents is higher than our monthly number of Uploaded documents. Here’s the chart we’re working with:
We’ll add a Filter Rows step in the Pipeline (+Add Transformation > Filter Rows) with the following settings:
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
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’ll 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:
Our next step is to add an Add Column step in the Pipeline. We’ll call this column DOW (i.e., Day of Week), select to use a Custom formula as the Formula Type, and use the following formula, replacing Day of Created Date with your column name:
strftime('%w', "Day of Created Date")
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:
And that’s it! We’ve now excluded all the Saturday and Sunday data. You can now hide the DOW 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 Controls 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 Control is set to only one date, you won’t need to append anything to the Control’s name and can input it as is.
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 automatically changes according to the current day and filters the chart without needing manual changes.
