Support Portal

Sendwise Filter Expressions

Filter expressions are an advanced way to filter Sendwise queries, and this page describes how to write them. In the Explore section of Sendwise you can use them by adding a filter and choosing the matches (advanced) option. 

 String

ExampleDescription
FOOis equal to “FOO”, exactly
FOO,BARis equal to either “FOO” or “BAR”, exactly
%FOO%contains “FOO”, matches “buffoon” and “fast food”
FOO%starts with “FOO”, matches “foolish” and “food” but not “buffoon” or “fast food”
%FOOends with “FOO”, matches “buffoo” and “fast foo” but not “buffoon” or “fast food”
F%ODstarts with an “F” and ends with “OD”, matches “fast food”
EMPTYstring is empty (has zero characters) or is null (no value)
NULLvalue is null (please remember to put NULL in quotes) 
-FOOis not equal to “FOO” (is any value except “FOO”), matches “pizza”, “trash”, “fun” but not “foo”
-FOO,-BARis not equal to either “FOO” or “BAR”, matches any value except “FOO” and “BAR”
-%FOO%doesn’t contain “FOO”, does not match “buffoon” or “fast food”
-FOO%doesn’t start with “FOO”, does not match “foolish” or “food”
-%FOOdoesn’t end with “FOO”, does not match “buffoo” or “fast foo”
-EMPTYstring is not empty (has at least one character)
-NULLvalue of column is not null
FOO%,BARstarts with “FOO” or is “BAR” exactly, matches “food” and matches “bar” but not “barfood”
FOO%,-FOODstarts with “FOO” but is not “FOOD”
_UFhas any single character followed by “UF”, matches “buffoon”

Including Special Characters in String Filters

Note these rules for including special characters in string filters:

  • To include % or _, prefix with the escape character, ^. For example: ^% and ^_
  • To include ^, escape it as ^^.
  • To include a comma in a string filter, prefix the comma with a backslash character, \. For example: Santa Cruz\, CA.
  • To include a comma with the matches (advanced) option in a filter, prefix the comma with the escape character, ^. For example: Santa Cruz^, CA.
  • To include a comma in a filter expression in Sendwise, prefix with the escape character, ^. For example: 


    field: filtered_count {

      type: count

      filters: {

        field: city

        value: "Santa Cruz^, CA"

      }

    }

Date & Time

Sendwise date filtering allows for English phrases to be used instead of SQL date functions. For all the examples below assume today is Friday, 2018/05/18 18:30:02. In Sendwise Analytics, weeks start on Monday.

Basic Structure of Date and Time Filters

For the following examples …

  • {n} is an integer.
  • {interval} is a time increment such as hours, days, weeks, or months.

    The phrasing you use determines whether the {interval} will include partial time periods or only complete time periods. For example, the expression 3 days includes the current, partial day as well as the prior two days. The expression 3 days ago for 3 days includes the previous 3 complete days and excludes the current, partial day. See the Relative Dates section for more information.

  • {time} is a time of the form YYYY-MM-DD HH:MM:SS or a date of the form YYYY-MM-DD.

These are all the possible combinations of date filters:

  • this {interval} - For example: this day
  • {n} {interval} - For example: 3 days
  • {n} {interval} ago - For example: 3 days ago
  • {n} {interval} ago for {n} {interval}
  • before {n} {interval} ago
  • before {time}
  • after {time}
  • {time} to {time}
    The initial time value is inclusive. The latter time value is not. So the expression 2018-05-18 12:00:00 to 2018-05-18 14:00:00 will return data with the time “2018-05-18 12:00:00” through “2018-05-18 13:59:59”.
  • {time} for {n} {interval}
  • today
  • yesterday
  • tomorrow
  • next {week, month, quarter, fiscal quarter, year, fiscal year}
    The next keyword is unique in that it requires one of the intervals listed above, and will not work with other intervals.
  • {n} {interval} from now
  • {n} {interval} from now for {n} {interval}

Date filters can also be combined together:

  • To get OR logic: Type multiple conditions into the same filter, separated by commas. For example today, 7 days ago means “today or 7 days ago”.
  • To get AND logic: Type your conditions, one by one, into multiple date or time filters. For example you could put after 2014-01-01 into a Created Date filter, then put before 2 days ago into a Created Time filter. This would mean “after January 1st, 2014 and before 2 days ago”.

Absolute Dates

Absolute date filters use the specific date values to generate query results. These are useful when creating queries for specific date ranges.

ExampleDescription
2018/05/29sometime on 2018/05/29
2018/05/10 for 3 daysfrom 2018/05/10 00:00:00 through 2018/05/12 23:59:59
after 2018/05/10after 2018/05/10 23:59:59
before 2018/05/10before 2018/05/10 00:00:00
2018/05within the entire month of 2018/05
2018/05 for 2 monthswithin the entire months of 2018/05 and 2018/06
2018/05/10 05:00 for 5 hoursfrom 2018/05/10 05:00:00 through 2018/05/10 09:59:59
2018/05/10 for 5 monthsfrom 2018/05/10 00:00:00 through 2018/10/09 23:59:59
2018entire year of 2018 (2018/01/01 00:00:00 through 2018/12/31 23:59:59)
FY2018entire fiscal year starting in 2018
FY2018-Q1first quarter of the fiscal year starting in 2018

Relative Dates

Relative date filters allow you to create queries with rolling date values relative to the current date. These are useful when creating queries that update each time you run the query.

Seconds

ExampleDescription
1 secondthe current second (2018/05/18 18:30:02)
60 seconds60 seconds ago for 60 seconds (2018/05/18 18:29:02 through 2018/05/18 18:30:01)
60 seconds ago for 1 second60 seconds ago for 1 second (2018/05/18 18:29:02)

Minutes

ExampleDescription
1 minutethe current minute (2018/05/18 18:30:00 through 18:30:59)
60 minutes60 minutes ago for 60 minutes (2018/05/18 17:30:00 through 2018/05/18 18:29:59)
60 minutes ago for 1 minute60 minutes ago for 1 minute (2018/05/18 17:30:00 through 2018/05/18 17:30:59)

Hours

ExampleDescription
1 hourthe current hour (2018/05/18 18:00 through 2018/05/18 18:59)
24 hoursthe same hour of day that was 24 hours ago for 24 hours (2018/05/17 18:00 through 2018/05/18 17:59)
24 hours ago for 1 hourthe same hour of day that was 24 hours ago for 1 hour (2018/05/17 18:00 until 2018/05/17 18:59)

Days

ExampleDescription
todaythe current day (2018/05/18 00:00 through 2018/05/18 23:59)
2 daysall of yesterday and today (2018/05/17 00:00 through 2018/05/18 23:59)
1 day agojust yesterday (2018/05/17 00:00 until 2018/05/17 23:59)
7 days ago for 7 daysthe last complete 7 days (2018/05/11 00:00 until 2018/05/17 23:59)
today for 7 daysthe current day, starting at midnight, for 7 days into the future (2018/05/18 00:00 until 2018/05/24 23:59)
last 3 days2 days ago through the end of the current day (2018/05/16 00:00 until 2018/05/18 23:59)
7 days from now7 days in the future (2018/05/25 00:00 until 2018/05/25 23:59)

Weeks

ExampleDescription
1 weektop of the current week going forward (2018/05/14 00:00 through 2018/05/20 23:59)
this weektop of the current week going forward (2018/05/14 00:00 through 2018/05/20 23:59)
before this weekanytime until the top of this week (before 2018/05/14 00:00)
after this weekanytime after the top of this week (2018/05/14 00:00 and later)
next weekthe following Monday going forward 1 week (2018/05/21 00:00 through 2018/05/27 23:59)
2 weeksa week ago Monday going forward (2018/05/07 00:00 through 2018/05/20 23:59)
last weeksynonym for “1 week ago”
1 week agoa week ago Monday going forward 1 week (2018/05/07 00:00 through 2018/05/13 23:59)

Months

ExampleDescription
1 monththe current month (2018/05/01 00:00 through 2018/05/31 23:59)
this monthsynonym for “0 months ago” (2018/05/01 00:00 through 2018/05/31 23:59)
2 monthsthe past two months (2018/04/01 00:00 through 2018/05/31 23:59)
last monthall of 2018/04
2 months agoall of 2018/03
before 2 months agoall time before 2018/03/01
next monthall of 2018/06
2 months from nowall of 2018/07
6 months from now for 3 months2018/11 through 2019/01

Quarters

ExampleDescription
1 quarterthe current quarter (2018/04/01 00:00 through 2018/06/30 23:59)
this quartersynonym for “0 quarters ago” (2018/04/01 00:00 through 2018/06/30 23:59)
2 quartersthe past two quarters (2018/01/01 00:00 through 2018/06/30 23:59)
last quarterall of Q1 (2018/01/01 00:00 through 2018/03/31 23:59)
2 quarters agoall of Q4 of last year (2017/010/01 00:00 through 2017/12/31 23:59)
before 2 quarters agoall time before Q4 of last year
next quarterall of the following quarter (2018/07/01 00:00 through 2018/09/30 23:59)
2018-07-01 for 1 quarterall of Q3 (2018/07/01 00:00 through 2018/09/30 23:59)
2018-Q4all of Q4 (2018/10/01 00:00 through 2018/12/31 23:59)


ExampleDescription
1 yearall of the current year (2018/01/01 00:00 through 2018/12/31 23:59)
this yearall of the current year (2018/01/01 00:00 through 2018/12/31 23:59)
next yearall of the following year (2019/01/01 00:00 through 2019/12/31 23:59)
2 yearsthe past two years (2017/01/01 00:00 through 2018/12/31 23:59)
last yearall of 2017
2 years agoall of 2016
before 2 years agoall time before 2016/01/01

Boolean

Filtering on true or false type values in Sendwise requires you to know what type of true or false value you’re interacting with.

ExampleDescription
yesfield evaluates to true
nofield evaluates to false
TRUEfield contains true (for fields that contain Boolean database values)
FALSEfield contains false (for fields that contain Boolean database values)

Number

Filters on numbers support both natural language expressions (for example 3 to 10) and relational operators (for example >20). Sendwise supports the OR operator to express multiple filter ranges (for example 3 to 10 OR 30 to 100). The AND operator can be used to express numeric ranges with relational operators (for example >=3 AND <=10) to specify a range.

ExampleDescription
5is exactly 5
not 5
<> 5
!= 5
is any value but exactly 5
1, 3, 5, 7is one of the values 1, 3, 5 or 7, exactly
not 66, 99, 4is NOT one of the values 66, 99 or 4, exactly
5.5 to 10
>= 5.5 AND <=10
is 5.5 or greater but also 10 or less
not 3 to 80.44
<3 OR >80.44
is less than 3 or greater than 80.44
1 to
>= 1
is 1 or greater
to 10
<=10
is 10 or less
>10 AND <=20 OR 90is greater than 10 and less than or equal to 20, or is 90 exactly
>=50 AND <=100 OR >=500 AND <=1000is between 50 and 100, inclusive, or between 500 and 1000, inclusive
NULLhas no data in it
NOT NULLhas some data in it 

Interval Notation

Filters on numbers can also use algebraic interval notation to filter numeric fields.

Open Interval

ExampleDescription
(a, b)interpreted as a < x < b where the endpoints are NOT included

While this notation resembles an ordered pair, in this context it refers to the interval upon which you are working.

Closed Interval

ExampleDescription
[a, b]interpreted as a <= x <= b where the endpoints are included

Half-open Interval

ExampleDescription
(a, b]interpreted as a < x <= b where a is not included, but b is included
[a, b)interpreted as a <= x < b where a is included, but b is not included

Non-ending Interval

ExampleDescription
(a, inf)interpreted as x > a where a is not included and infinity is always expressed as being “open” (not included)
(-inf, a]interpreted as x <= b where b is included and again, infinity is always expressed as being “open” (not included)

Note: inf may be omitted and the above may be written as (a,) or (,a]

Multiple Intervals

The union of multiple intervals may be expressed with a comma. For example:

ExampleDescription
[0,9],[20,29]the numbers between 0 and 9 inclusive or 20 to 29 inclusive

Intervals and Numbers at the Same Time

ExampleDescription
[0,10],200 to 10 inclusive or 20

Using NOT

ExampleDescription
NOT 10,[1,5)all numbers except 10, and except 1 up to but not including 5