Applying Search filters in Calculated Columns
It is possible to replicate all of the search filters from a Landing Page within a Report Calculated Column. This article will provide some examples of the formats you will need to follow in order to replicate searches you may perform on a landing page.
The Calculated Columns all use an IF Statement to output a True of False response which can then be filtered on within the Report with a calculated criteria. In some of these calculations you will notice that the percentage (%) symbol is used – this is a wild card operator meaning that anything can take its place in the field.
Starts with
If a Person’s name starts with ‘abc’ output True otherwise False.
IF(name LIKE ‘abc%’, ‘True’,’False’)
Contains
If a Person’s name contains ‘abc’ output True otherwise False.
IF(name LIKE ‘%abc%’, ‘True’, ‘False’)
Does not start with
If a Person’s name does not starts with ‘abc’ output True otherwise False.
IF(name NOT LIKE ‘abc%’, ‘True’, ‘False’)
Or
IF(name LIKE ‘abc%’, ‘False’, ‘True’)
Does not contain
If a Person’s name does not contains ‘abc’ output True otherwise False.
IF(name NOT LIKE ‘%abc%’, ‘True’, ‘False’)
Or
IF(name LIKE ‘%abc%’, ‘False’, ‘True’)
Equals
If a Person’s town is equal to London output True otherwise False
IF(town = ‘London’, ‘True’, ‘False’)
Does not equal
If a Person’s town is not London output True otherwise False
IF(town != ‘London’, ‘True’, ‘False’)
NOTE – Like with Landing Page Filters we would recommend using an equals calculation rather than a starts with or contains calculation as this will return Report results much faster.
Is not blank
If a Person’s town is not blank output True otherwise False
IF(town IS NOT NULL AND town != ‘’, ‘True’, ‘False’)
Is blank
If a Person’s town is blank output True otherwise False
IF(town IS NULL OR town = ‘’, ‘True’, ‘False’)
Is on or after
If a Created at date is on or after 1st January 2018 output True otherwise False.
IF(created_at >= ‘2018-01-01’, ‘True’, ’False’)
Is after
If a Created at date is after 1st January 2018 output True otherwise False.
IF(created_at > ‘2018-01-01’, ‘True’, ’False’)
Is before
If a Created at date is before 1st January 2018 output True otherwise False.
IF(created_at < ‘2018-01-01’, ‘True’ ,’False’)
Is on or before
If a Created at date is on or before 1st January 2018 output True otherwise False.
IF(created_at <= ‘2018-01-01’, ‘True’, ’False’)