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’)

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’)