Chat with us, powered by LiveChat

Get a quick quote with our pricing calculator

Knowledge Base

Browse our knowledge base articles to quickly solve your issue.

Knowledgebase articles

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