Use the Expression Builder
Last updated
Last updated
The Expression Builder is used to create new fields defined by an expression or calculation. This expression consists of operators, constants, functions, and fields from one data table. (See more information below.) Fields may be numeric, string, or date. A separate tool, the , is also available for manipulating dates and provides a wider set of formatting options.
You can find videos on how to use the Expression Builder, along with some calculations examples in our .
Select Build Expressions from the Author Ribbon or click the Expression Builder button in the toolbar.
Elements of the user interface are:
Primitives tab below: This tab contains a tree that shows the data fields available in the current table and the operators, functions, and constants that can be used to create expressions. You can use this for reference, or drag (or double-click) items from it into the expression edit box to construct your expression.
Help tab: This tab displays Expression Builder help.
Table: choose a table name.
Calculated Field: existing calculated fields in the current table are shown in the drop-down list; type into this box to name a new field.
Expression edit box: Define your expression her by typing directly into this box, dragging elements from the tree, or clicking elements in the tree.
New Field Preview: After you have created an expression, this will show the first 25 rows of the input rows and the created field so that you can check that the expression created what you expected.
Result type: After a new field is created, this shows the type of that field. Types will be string, integer, double, or date.
Evaluate: Click this to create a new field using the specified expression.
Clear: Clears input so that you can start over.
Delete: Deletes the field show in the "Calculated Field" list box.
Cancel: Cancels all changes made and closes the dialog; the state of the data is as it was when the dialog was started.
OK: Exit from the dialog leaving all changes.
Select the table to contain the new field from the "Table" box.
Name your new field in the "Calculated Field" box.
Type the expression or click or drag fields, operators, functions, and constants from the language box.
Click "Evaluate" to create the new field and show its first 25 rows.
Exit keeping the new field by clicking "OK". Exit without keeping the new field by clicking "Cancel".
Select the table to contain the new field from the "Table" box.
Select the name of the existing field from the "Calculated Field" box.
Modify the expression by typing or clicking or dragging fields, operators, functions, and constants from the language box.
Click "Evaluate" to modify the field and show its first 25 rows.
Exit keeping the modification by clicking "OK". Exit without keeping the change by clicking "Cancel".
Select the table to contain the new field from the "Table" box.
Select the name of the existing field from the "Calculated Field" box.
Click the "Delete" button.
Exit with this change by clicking "OK". Exit without the deletion by clicking "Cancel".
Be sure that the Usage tab is displayed. From the Table pulldown list, select the table that contains the calculated field(s) that you have to view. A list of calculated fields is displayed.
To delete one or more of these fields from the project, click the checkbox for each field to be deleted (a checkmark should appear in the checkbox). Click the Delete button.
Charts that use a calculated field may be found with the Usage button.
Beware of expressions that produce semi-additive measures! Semi-additive measures are meaningless if they are aggregated by summing. For example, you can calculate the percentage of one field versus another (e.g., "Int( FieldA / FieldB * 100)"). But if you weight a Bar Chart by this new field, it will be weighted by the sums of the percentages of the rows that have that category. This is not a useful weight! In this case, you should use the "Ratio" weight in the bar chart instead to yield a weight that aggregates correctly.
Case and whitespace in expressions are not significant. Operator precedence follows standards mathematical standards (and follow the rules of the C programming language).
A "#" (an "octothorpe") begins a comment, which runs to a new line or the end of the expression string. This is ignored.
An expression is evaluated for each row in a table, and the result of the expression is added as the value of a new field to that table.
Integers and double values may be used interchangeably, but otherwise there are not implicit type conversions. You must explicitly convert types using the "string()", "double()", or "int()" functions.
All tokens of the language (e.g., function names, constants) are case independent: "now" and "Now" will both be recognized. Field names must match case, however.
See "Primitives" below for a discussion of legal field names, strings, and quoting.
The names in angle brackets used below indicate a class of characters.
Item
Meaning
<integer>
Any integer number. Examples are: 0, 1, 11, 243.
<real>
Any real (decimal) number. Decimal constants in expressions must be entered in US format, with a "." as the decimal point. Examples are: 0.1, 1.1, 3.14159.
'Any string'
A literal string is enclosed by single quotes (') and may contain any printable character.
<fieldname>
The name of a field in the target table. The value of that field for the current row is substituted. Names starting with an alphabetic character and consisting of only alphanumeric characters and underscore ("_") do not need to be quoted. If the field name contains other characters, such as spaces, the name must be quoted. A field name that matches an Expression Builder reserved word, such as a function name, must be quoted. Use back quotes (`) for a field name. Double quotes may also be used.
`A field name`
A field name enclosed by back quotes (`) and may contain any printable character including space. Quoting may also be used if a field name is the same as an Expression Builder reserved word, such as a function name.
"string"
A string surrounded with double quotes that matches a field name in the current table will be taken as that field name; otherwise it is a literal string. This usage is not recommended since an invalid reference to a field name may not be detected; use back quotes instead.
'mm/dd/yy'
Constant dates are given as strings using the format of the current locale; time is optional. Constant dates are currently only valid in comparison operators.
i
Current row index, starting at 1.
n
Number of rows in the table.
missing
A missing value in a field. A missing value indicates the absence of data. Most charts ignore missing data or display it optionally. Missing values may be present in the source data. In general a missing value in an expression will cause the result of evaluating the expression to be "missing" as well, since most operations combined with "missing" result in "missing". Missing values may be tested for (using the "if ... then ... else ..." expression) and created in the output field (again typically using an "if ... then ... else ..." expression).
now
The current date and time. This can be broken into year, month, or day using date functions (see below), and can be subtracted from a date field value to find the number of days between the dates.
nowdataload
The date and time when the data was loaded. For an "adv" project that loads data, this will be basically the same as "now". For an "advm" project that includes saved data, this will be the date/time when that project was created. Use this if you will be working on a project with saved data for a period of time and adding new calculations which you want to all be relative to the same current point in time. Case is irrelevant, so you can use "NowDataLoad" or other alternatives as well.
Function
Meaning
mean(field)
The mean or average of the field.
median(field)
The median, the middle value from the field after it is ordered. The middle of the sample.
stddev(field)
The standard deviation of the field values.
sigma(field), var(field), variance(field), sigma2(field)
The variance of the field values, the standard deviation squared.
norm(field)
The normalized value, (x - mean) / stddev
order(field)
Integer index of the value in the current row in sorted order. The median is the valued indexed by the middle value in the order.
rank(field)
Rank of numbers in the field.
min(field)
The minimum value in the field.
max(field)
The maximum value in the field.
bin(field, integer)
Create an integer bin number based on the field values. The range of the field is divided into "integer" bins, and the bin number is used as the value of the calculated field. There is another variation of bin() for string fields; see "Functions taking String Expressions".
sum(field)
Sum of values in the field.
Function
Meaning
sqrt(expr), root(expr)
Square root.
log(expr)
Natural log.
log10(expr)
Logarithm base 10.
exp(expr)
Exponential.
exp10(expr)
Base 10 exponential.
abs(expr)
Absolute value.
sign(expr)
1.0 if the number is positive, -1.0 if the number is negative, and 0.0 if the number is 0.0.
int(expr)
The expression value truncated to an integer (if a double) or converted from a string.
double(expr)
The expression value as a real or double. The expression may be a string or an integer.
min(expr, expr, ...)
Minimum of list.
max(expr, expr, ...)
Maximum of list
string(expr, ["format"])
Converts expression to a string. The optional second parameter is a string specifying how the conversion of a number is to be done. The values for this format are given below. This is most commonly used to give the precision, e.g., "%.2f" gives 2 decimal places. Since all numbers are internally represented as reals, to format an integer without a decimal fraction, use this format: "%.0f".
distance(lat1, lon1, lat2, lon2)
The "distance()" function calculates the Great Circle distance between two locations in latitude and longitude. The distance is in miles. Either of the 2 points may be constant or from data fields in a table. To compute the distance of all rows in a table containing latitude and longitude from a single point, make one of the latitude/longitude pairs constant values. Latitudes and longitudes are coded as decimal numbers. The distance is based on an arc that follows the curvature of the earth, but does not follow roads. This is calculated with the Haversine formula.
You can find the constant position latitude and longitude using data added by the Map chart or by looking in one of the the text files of location information that are available in the ADVIZOR Analyst installation directory, typical "c:/Program Files/ADVIZOR Solutions/Analyst/Maps".
You can also find the latitude and longitude for a location using mapping software. For example, using Google Maps from a web browser, click on the map to set a pin, right click and select "What's here?". The displayed box includes the latitude and longitude of that location.
The format for a numeric result may be specified by appending "as <format>", where formats are "n[0-9]", "d", or "c[0-9][$]". "[0-9]" specifies an optional number of positions after a decimal point; the brackets are not actually used in the specification. An optional currency indicator may be given at the end of the "c" format.
Format Code
Meaning
n[0-9]
Format as number with optional precision. A thousand separator (",") will be used. Examples are "n" (0 precision), "n0" (0 precision), "n2" (2 digit precision).
d
Format as integer number with no thousand separator (","). This can be used if the result is a year, of example, where a thousand separator is unexpected.
c[0-9][Currency]
Format as currency with optional precision and currency indicator. If "Currency" is given, then there must be a precision. Currency may be a single character (e.g., "$") or a string of characters. If a currency string is not given, the default for the locale is used. If a currency indicator is given, it is positioned before or after the number according to the locale specification. A thousand separator (",") will be used.
Examples are "c" (0 precision, currency symbol, thousand separator), "c2" (2 digit precision, currency symbol, thousand separator), "c2EUR" (2 digit precision, "EUR" as currency indicator, thousand separator).
To enter a particular currency symbol that is not directly on your keyboard, look up key sequences that may be used or find a sample and paste into the expression editor window. For example, the Euro symbol on windows may be entered as ALT+0128 using the number keypad, and the GBP symbol as ALT+0163.
For example, here is an expression that will be formatted as currency, with a thousands separator, and no decimal places:
amt / 10.0 as c
For example, here is an expression that will be formatted as currency, with a thousands separator, and 2 decimal:
amt / 10.0 as c2
The result of this expression will be formatted as an integer with no thousand separator and no decimal places of precision:
amt / 10.0 as d
This result of this expression will be formatted with a thousand separator and 1 digit of precision:
amt / 10.0 as n1
See Examples below for additional examples.
Function
Meaning
bin(`stringfield`, threshold, "bin name")
Keeps categories used in "threshold" (typically 95%) of data, and groups all remaining categories into a single new category named "bin name". This is useful for eliminating large numbers of small occurrence categories while maintaining the categories for the majority of the data. This is especially useful in preparing data for use in Analytics.
double(string)
Convert string expression to a double. If the string is not a valid number, then missing.
int(string)
Convert string expression to an integer. If the string is not a valid number, then missing.
substring(string, start, [length])
Substring of string expression beginning a start position (1 index) for "length" characters. If length is omitted, the remainder of the string.
match(string, pattern)
Returns "1" if pattern matches the string expression, otherwise "0". Syntax for patterns is given below.
sub(string, pattern, replacement)
For string "expression", for the substring matched by "pattern", replace with "replacement". Matches for occurrence of pattern.
gsub(string, pattern, replacement)
Replaces all occurrences of "pattern" in string "expr" with string "replacement".
lower(string)
Convert string to all lower case.
upper(string)
Convert string to all upper case.
+
Plus operator concatenates 2 strings.
Function
Meaning
- (subtraction)
You can subtract two dates, or subtract a number from a date.
Subtracting two dates gives the interval between them in days, as a floating point number. The fraction of a day represents the hours/minutes between the dates. You often want to convert this to an integer ("int()") if the elapsed days will be used in a categorical view such as a bar chart and you really only want days.
From the interval between two days, you can convert days to years (based on an average of 365.25 days per years) or months (based on an average of 30.42 days per month). If you wish working days only and absolute accuracy is not needed, use 5/7 (0.71) of the elapsed days. Use the token "now" for the current date and time.
Subtracting a number from a date offsets finds a new date that many days in the past. The offset is always an integer number of days.
+ (addition)
Adding a number to a date increments the date by that number of days. Only integer numbers of days are added; fractions are ignored. If the date being incremented contains a time, it is unchanged.
string(date)
Converts date to a string.
date(year, month, day)
Create a new date from a year, month, and day. A time cannot be created. "Year" must be a 4 digit number. "Month" is a positive or negative number.
- If "months" is greater than 12, month adds that number of months to the first month in the year specified. For example, date(2008, 14, 2) returns the date February 2, 2009.
- If "month" is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the year specified. For example, date(2008, -3, 2) returns the date September 2, 2007.
"Day" is a positive or negative integer representing the day of the month from 1 to 31.
- If "day" is greater than the number of days in the month specified, days adds that number of days to the first day in the month. For example, date(2008, 1, 34) returns the date February 4, 2008.
- If "day" is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. For example, date(2008, 1, -15) returns December 16, 2007.
"Date()" may be used to create a fiscal date by incrementing the "month" field. For example, if a Fiscal Year begins on 7/1, this will create the fiscal date from a real date: date(year(`date`), month(`date`)+6, 1).
month(date)
Return month portion of a date as an integer.
day(date)
Return day portion of a date as an integer.
year(date)
Return year portion of a date as an integer.
All logical operators are available for comparing dates. The "now" and "nowdataload" constant can be used in a comparison or a computation; see "Primitives" section. Comparisons to constant dates may be done using a string in the locale date format (e.g., "mm/dd/yy") or to a date created with the "date()" function.
The separate Date Calculator can also be used to convert dates into a large number of equivalent formats for display as strings.
Function
Meaning
subnet(field, 'cidr_IP_address')
For a string field that contains IP addresses, match with an IP address in CIDR notation and return 1 if they match, 0 if they don't. Both IPv4 and IPv6 addresses are supported. The constant address that is matched is an IP address in dot notation followed by an optional "/" and a mask length. This must be a string. The mask length is the number of bits from the start of the IP address that will be matched. For example, for IPv4 the specification "192.168.0.0/16" matches any IP address in "field" that starts with "192.168". The mask length is in bits, and may be any number less than 32 (for an IPv4 address). For IPv6 addresses, standard ":" notation is used and the mask length can be any number up to 128. Multiple 0 entries in an IPv6 address may be specified with double colons ("::").
Note that the Date Calculator can also be used to convert dates into a large number of equivalent formats.
Most expressions are used to create a new field in a table using other fields in that table. The "link()" function is unique in that it retrieves data from another table for use in creating a new field.
Function
Meaning
link('key1', 'table2', 'key2', 'expression', 'aggregation') and link('table2', 'expression', 'aggregation')
For each row in another table, 'table2', find the rows that have matching values for 'key1' in the current table in field 'key2' for those rows, apply an expression 'expression', then combine the results together using 'aggregation'. Expression is any legal expression using any fields from table2. Aggregation is defined in the table below.
All parameters must be quoted strings. Quoting with single quotes ("'") works best.
Processing works like this:
- For each row in the current table:
- Find the set of rows in 'table2' that have matching values to 'key1' in field 'key2'.
- Process each of these rows with the expression. The expression may use data from any field in the second table. The result of the expression is a value that is returned.
- Combine the set of result values into a single value for the row.
- Repeat for each row in the table.
The second form takes only the name of the second table; an existing link (such as a selection link) is found and used to establish the connection between the tables. The first link found is used.
See link() Examples below for samples.
Aggregation Operation
Meaning
Applies to types
Result type
any
The first non-missing value. A missing value is only returned if all input values are missing.
Integer, Double
Double
average
Average
Integer, Double
Double
max
Maximum value
Integer, Double, Date, String
Input type
min
Minimum value
Integer, Double, Date, String
Input type
sum
Accumulate as sum
Integer, Double
Double
distinct
Count of unique values. May also be given as 'unique'.
Integer, Double, Date, String
Double
Operator
Meaning
( )
Parenthesis, for grouping.
+
Addition for number, concatenation for strings
-
Subtraction. If operands are dates, the result is the time difference in days.
*
Multiplication
/
Division
%
Modulo
div
Integer division
mod
Integer modulo
^
Exponentiation
The result of a logical operator is an integer "0" (false) or "1" (true).
Operator
Meaning
&&
Logical and
| |
Logical or
!
Not
== or =
Equals
! =
Not equal
<=
Less than or equal
<
Less than
>
Greater than
>=
Greater than or equal
The set operator "in" is used to test if a field value is contained in a specified set of values. This is a short hand to replace a sequence of tests: "field == a || field = b || field = c || ....
Operator
Meaning
<fieldname> in {v1, v2, ..., vn)
Test if values from "fieldname" are in the set of constant literal values. The values must be numbers, strings, or dates, according to the type of the field "fieldname".
Operator
Meaning
condition ? expr1 : expr2
If the condition is true, the result is "expression1"; otherwise the result is "expression2". Conditionals can be nested to form "if ... then ... else if ..." chains. When nesting, use parentheses to group. For example, consider this expression:
a = "b" ? e1 : a = "c" ? e2 : e3
This means:
(a = "b" ? e1 : a = "c") ? e2 : e3
but you probably intend:
a = "b" ? e1 : (a = "c" ? e2 : e3)
Use parentheses so that it is interpreted as you intend.
if <condition> then <expr1> else <expr2>
Alternative syntax for "if ... then ... else ...". This form does not require that the "else" clause be enclosed with parentheses.
Numbers are stored internally as doubles, so the format specification describes how to format a double as a number. The format for a number converted to a string may be specified using a string with this format:
The number is substituted for the format specification, which begins with a "%". Any other characters are included in the output but don't affect formatting. Format specifications are:
%f: decimal notation; use ".n" to specify precision, e.g., "%.2f". Use "%.0f" if you don't want a fractional part (e.g., the number is really an integer).
%e: use scientific notation
%E: scientific notation with "E" for exponent
%g: floating point or scientific notation, whichever is smaller
The default format uses "%f".
The default format uses "%f".
Pattern matching functions accept string that are patterns using this syntax. An element can be one of the following things:
An ordinary character that matches the same character in the target sequence.
A wildcard character '.' that matches any character in the target sequence except a newline.
A bracket expression of the form "[expr]" which matches a character or a collation element in the target sequence that is also in the set defined by the expression expr or of the form "[^expr]" which matches a character or a collation element in the target sequence that is not in the set defined by the expression expr. The expression expr can contain any combination of the following things:
An individual character. Adds that character to the set defined by expr.
A character range of the form "ch1-ch2". Adds the characters that are represented by values in the closed range [ch1, ch2] to the set defined by expr.
A character class of the form "[:name:]". Adds the charactgers in the named class to the set defined by expr. Named classes are "alnum" (letter and digits), "alpha" (letters), "blank" (space or tab), "ctrnl", "digit", "graph" (lowercase letters, uppercase letters, digits, punctuation), "lower", "print" (lowercase and uppercase letters, digits, punctuation, and space), "punct", "space", "upper", "xdigit" (a through f).
Anchor '^' matches the beginning of the target sequence; anchor '$' matches the end of the target sequence.
A capture group of the form "(subexpression)" which matches the sequence of characters in the target sequence that is matched by the pattern between the delimiters.
An identity escape of the form "\k" which matches the character k in the target sequence and removes any special meaning. For example "a**" matches "aaa", but does not match "a*", while "a_" does NOT match "aaa", but does match "a_".
Examples:
"a" matches the target sequence "a" but does not match the target sequences "B", "b", or "c".
"." matches all the target sequences "a", "B", "b", and "c".
"[b-z]" matches the target sequences "b" and "c" but does not match the target sequences "a" or "B".
"[:lower:]" matches the target sequences "a", "b", and "c" but does not match the target sequence "B".
"(a)" matches the target sequence "a" and associates capture group 1 with the subsequence "a", but does not match the target sequences "B", "b", or "c".
Any element other than a positive assert, a negative assert, or an anchor can be followed by a repetition count. The most general kind of repetition count takes the form "{min,max}". An element that is followed by this form of repetition count matches at least min successive occurrences and no more than max successive occurrences of a sequence that matches the element. For example, "a{2,3}" matches the target sequence "aa" and the target sequence "aaa", but not the target sequence "a" or the target sequence "aaaa". A repetition count can also take one of the following forms:
"{min}". Equivalent to "{min,min}".
"{min,}". Equivalent to "{min,unbounded}".
"*". Equivalent to "{0,unbounded}".
Examples:
"a{2}" matches the target sequence "aa" but not the target sequence "a" or the target sequence "aaa".
"a{2,}" matches the target sequence "aa", the target sequence "aaa", and so on, but does not match the target sequence "a".
"a*" matches the target sequence "", the target sequence "a", the target sequence "aa", and so on.
A repetition count can also take one of the following forms:
"?". Equivalent to "{0,1}".
"+". Equivalent to "{1,unbounded}".
Examples:
"a?" matches the target sequence "" and the target sequence "a", but not the target sequence "aa".
"a+" matches the target sequence "a", the target sequence "aa", and so on, but not the target sequence "".
Concatenation Regular expression elements, with or without repetition counts, can be concatenated to form longer regular expressions. The resulting expression matches a target sequence that is a concatenation of the sequences that are matched by the individual elements. For example, "a{2,3}b" matches the target sequence "aab" and the target sequence "aaab", but does not match the target sequence "ab" or the target sequence "aaaab".
Alternation A concatenated regular expression can be followed by the character '|' and another concatenated regular expression. Any number of concatenated regular expressions can be combined in this manner. The resulting expression matches any target sequence that matches one or more of the concatenated regular expressions.
1
with a constant value of "1". Give this a meaningful name based on the thing that each row represents (e.g., "# Customers" if a row is a single customer). This can be used instead of the standard "count" aggregation since that isn't available within the Expression Builder expressions or Bar Chart ratio aggregations.
"abc"
New string field with values "abc".
x > (2 * median(x)) ? 1 : 0
New field has value "1" where the value of field "X" is greater than twice the median value, otherwise value "0".
x - mean(x)
New field contains difference between current row value and the mean.
order(x)
New field has sorted order index of values in numeric field "x".
int( x / 8 )
New integer field has the value of numeric field "x" that contains a number of hours converted to an integer number of 8 hour days.
(Employees <= 100) ? 100 :
((Employees <= 250) ? 250 :
((Employees <= 500) ? 500 :
((Employees <= 750) ? 750 :
(Employees <= 1000) ? 1000 : 1500)))
if ... then ...else expressions can be nested to apply a series of tests; this creates bins for number of employees into groups of 100 and under, 101 - 250, 251 - 500, 501 - 750, 751 - 100, and over 1000. It may be easiest to create this outside of the Expression Builder using a text editor and then copy and paste it into the Expression Builder.
Note that parentheses must be used to nest the conditional expressions in the "else" clause correctly.
if field1 = "cp" then "y" else "n"
New string field with values of "y" or "n" based on whether field "field1" has value "cp".
Field1 / (Field2 == missing ? 1 : Field2)
Divide Field1 value by Field2; if Field2 value is missing, then divide by 1.
'A' + `Field A` + FieldB
Concatenate the string literal "A" with the string field "Field A" and the string field "FieldB". "Field A" and FieldB must both be string fields; if not, convert to strings with the "string()" function.
match(FieldA, "^[0-9]*$")
New field with value "1" if FieldA value is a number (matches the pattern that defines a sequence of numeric digits) or "0" if not a number.
match(FieldA, "^[0-9]*$") ? 1 : missing
New field with value "1" if FieldA contains a string that is a number, or missing if not.
if FieldA = missing then 0 else FieldA
New field with values of FieldA with missing values replaced by "0".
year(Date)
The 4 digit year from a date.
int(now - Date)
Elapsed days from a date to today as an integer. The elapsed days returned by the subtraction is a float, which is not ideal for use in categorical charts such as bar charts.
int(NowDataLoad - Date)
Elapsed days from a date to when the data was loaded as an integer. Use this form if you are working for a period of time with saved data and want calculations to all be based on the same "now".
year(now) - year(Date)
Number of years between date and today.
(now - Date) * 0.714
Approximate working days between Date and today (0.714 ~= 5/7).
date(`year`, `month`, `day`)
Create a date value from 3 separate numeric fields containing year, month, and day.
date(year(d)+1, month(d), day(d))
Make a new date one year later from an existing date.
date(year(`date`), month(`date`)+6, 1)
Shift a date to a fiscal date with the Fiscal Year starting on 7/1.
if `date` < date(2015, 6, 1) then missing else `date`
Convert all dates before 6/1/2015 to missing. The "date()" function could also have been represented as the string '6/1/2015'.
if `date1` < `date2` then 1 else 0
Compare two dates and return "1" if `date1` is smaller, otherwise "0".
`date1` + 5
Create a date that is five days after the current value in field `date1`.
match(Date, "Q3") ? string( (int( substring(Date, 1, 4) ) + 1), "%.0f") + "Q1" :
(match(Date, "Q4") ? string( (int( substring(Date, 1, 4) ) + 1), "%.0f") + "Q2" :
(match(Date, "Q1") ? substring(Date, 1, 4) + "Q3" :
(substring(Date, 1, 4) + "Q2") ) )
Shift a string in year/quarter format to a June 30 fiscal year:
2007Q3 to 2008Q1
2007Q4 to 2008Q2
2008Q1 to 2008Q3
2008Q2 to 2008Q4
This is a series of nested if ... then ... else ... expressions for the 4 possible quarters. If the existing field matches Q1 or Q2, the year substring is found and the shifted quarter is appended, Q3 or Q4 (respectively). If the existing field matches Q3 or Q4, the year substring is found, converted to an integer, incremented 1, converted back to a string with 0 precision and the shifted quarter appended. Note that the conversion of the year to a string must have 0 decimal places specified ("%.0f"), since the "integer" is represented internally as a float.
year as d
Format field as number with no thousand separator (",").
amt / 10.0 as c
Display field as currency, with thousand separator (",").
"FieldA" in {"a", "b", "c"}
True (non zero) if the value of FieldA is "a", "b", or "c". Note that the constant strings must be quoted.
"FieldB" in {1, 10, 100}
True (non zero) if the value of FieldA is 1, 10, or 100.
"FieldC" in {'10/1/2001', '10/1/2002', '10/1/2003'}
True (non zero) if the value of FieldC is any of the given dates. "FieldC" must be a date field. The date is formatted according to the current locale and must be a quoted string.
bin(`CityName`, 95, "Other")
Returns the existing category in the field "CityName" for all categories that appear in 95% of the data; returns "Other" for all other categories.
These examples show uses of the "link()" function to retrieve data from another table. These examples assume 2 tables, an "Entity" table with each row identified by an "id" field, and a second "Gift" table with rows matching "Entity" using field "id". There may be multiple "Gift" rows with the same "id"; each "id" in "Entity" is unique.
link('id', 'Gift', 'id', '1', 'sum')
The number of gifts (rows in "Gift" table with a matching "ID"). This works by evaluating the constant expression "1" for each row in "Gift" that has a matching "id" value; these values are then summed. This results in the count of matching rows.
link('id', 'Gift', 'id', 'if Year > 2006 then 1 else 0', 'sum')
The number of "Gift" rows with a "Year" value greater than 2006: the number of gifts since 2006.
link('id', 'Gift', 'id', 'Amount', 'sum')
The total "Amount" of matching "Gift" rows.
link('id', 'Gift', 'id', 'Amount', 'max')
The maximum "Gift" "Amount" for matching rows.
link('id', 'Gift', 'id', 'Amount', 'distinct')
The number of distinct gift amounts for each Entity row.
link('id', 'Gift', 'id', 'if Year > 2006 then Amount else 0', 'sum')
The total for the "Gift" table "Amount" field values for an "Entity" since "Year" 2006.
link('id', 'Gift', 'id', 'if year(GiftDate) > 2006 then year(GiftDate) else missing', 'unique')
The number of years since 2006 in which a gift was given. "GiftDate" is a complete date.
link('id', 'Gift', 'id', 'max(GiftDate)', 'any')
The maximum "GiftDate" value. Since a single value will be returned for each row in the "Gift" table, the aggregation operation "any" is used to stop on the first value found.
link('id', 'Gift', 'id', 'now - max(GiftDate)', 'any')
The number of days in the past for the most recent matching "Gift" row.
link('id', 'Gift', 'id', 'if GiftDate = max(GiftDate) then Amount else missing', 'any')
The amount of the most recent gift. This finds the row in the "Gift" table whose "GiftDate" matches the maximum "GiftDate" for the rows with the same ID for the "Entity". "Any" is used as the aggregation operation to stop when this is found.
link('id', 'Gift', 'id', 'max(Amount) - min(Amount)', 'any')
The difference between the largest and smallest gift amount for a particular donor.
link('id', 'Gift', 'id', 'Amount', 'average')
The average "Amount" in the matching "Gift" table rows.
link('id', 'Gift', 'id', 'if Year > 2006 then Amount else missing', 'average')
The mean (average) "Amount" in the matching "Gift" table rows where the "Year" is greater than 2006. "Missing" values are ignored in the average calculation.
link('id', 'Degree', 'id', 'if Date = max(Date) then Degree else missing', 'any')
The "Degree" field in the "Degree" table that has the largest (most recent) date for matching rows.
link('Gift', '1', 'sum')
A count of the matching rows in the "Gift" table, using the first existing Link found (e.g., selection link or color link).
Latitude and longitude fields are added to your data, if not already present, when you configure a .
