Functions
Functions are used in an expression editor to build expressions that perform a variety of calculations and operations. See Expression Editor.
Types of functions
The type of data determines the functions that can be used. See Data Types.
A conditional function performs an action or calculation based on a test of data using an IF statement. Use a conditional function to provide a TRUE or FALSE result to highlight or filter out data based on specific criteria. Conditional functions can be used with any data type.
Consider each conditional function before writing your test. Some conditional functions are better suited to specific use cases.
IF c THEN t ELSE f ENDIF: IF condition THEN true ELSE false ENDIF
IF [Class]==1 THEN "Gold" ELSE "Other" ENDIF
Class  Result 

1  Gold 
2  Other 
1  Gold 
3  Other 
IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF: IF condition THEN true ELSEIF condition2 THEN true2 ELSE false ENDIF
Multiple ELSEIF statements can be included.
IF [Class]==1 THEN "Gold" ELSEIF [Class]==2 THEN "Silver" ELSE "Other" ENDIF
Class  Result 

1  Gold 
2  Silver 
1  Gold 
3  Other 
IIF(bool,x,y): If ([bool] is true) return (x), else return (y)
IIF([CUSTOMER], "Send flyer", "Send documentation")
If the [CUSTOMER] field value is TRUE, then it returns Send flyer.
If the [CUSTOMER] field value is FALSE, then it returns Send documentation.
Switch(Value,Default,Case1,Result1,...,CaseN,ResultN): Compares a value against a list of cases and returns the corresponding result.
Switch([Class], Null(), "Gold",1,"Silver",2,"Bronze", 3,"Tin", 4, "Aluminum",5)
Result  Class 

5  Aluminum 
2  Silver 
1  Gold 
3  Bronze 
4  Tin 
2  Silver 
1  Gold 
Null  Other 
A conversion function converts one data type or format to another data type or format. Use a conversion function to convert numbers to strings or strings to numbers. Conversion functions can be used with String and Number data types.
BinToInts(s): Converts the binary string s to an integer. (Limited to 53 bits.)
BinToInts(101010101) results in 341
CharFromInt(x): Returns the Unicode® character that matches the input number.
CharFromInt(66) returns B (U+0042 ‘Latin Capital Letter B’)
CharFromInt(169) returns © (U+00A9 ‘copyright sign’)
CharFromInt(1071) returns Я (U+042F ‘Cyrillic capital letter YA’)
CharFromInt(127944) returns (U+1F3C8 ‘American football’)
CharFromInt(0) returns [null] (U+000 ‘Null’) because any integer that cannot be used to represent a character may give a null result.
CharFromInt(55300) returns [null] because any integer that does not currently represent a character will not be rendered by a normal font.
CharToInt(S): Returns the number that matches the input Unicode® character.
CharFromInt(B) returns 66 (U+0042 ‘Latin Capital Letter B’)
CharFromInt(©) returns 169 (U+00A9 ‘copyright sign’)
CharFromInt(Я) returns 1071 (U+042F ‘Cyrillic capital letter YA’)
CharFromInt() returns 127944 (U+1F3C8 ‘American football’)
ConvertFromCodepage(s, codePage): Translates text from a code page to Unicode. See Code Pages.
ConvertToCodepage(s, codePage): Translates text from Unicode® encoding to a specific code page. See Code Pages.
HexToNumber(x): Converts a HEX string to a number. (Limited to 53 bits.)
HexToNumber(dd) converts to the number 221
IntToBin(x): Converts x to a binary string
IntToHex(x): Converts x to a hexadecimal string
ToNumber(x, bIgnoreErrors, keepNulls, decimalSeparator): Converts a string parameter to a number. ToNumber accepts strings that can be interpreted as scientific notation double precision. By default, the period is used as the decimal separator.
bIgnoreErrors: 0 or false (default) reports conversion error messages; 1 or true ignores conversion errors.
keepNulls: 0 or false (default) converts nonnumeric values (including null) to zero; 1 or true converts nonnumeric values to null.
decimalSeparator: "." (default) specifies the period as decimal separator; "," specifies the comma as decimal separator.
ToNumber("878") returns the string 878 as a number.
ToNumber("4.256411411E9") returns the string 4256411411 as a number.
ToNumber("Number", "false") returns 0 with conversion error: TONUMBER: Number lost information in conversion.
ToNumber("Number", 0, 0) returns 0 with conversion error: TONUMBER: Number lost information in conversion.
ToNumber("Number", 1, 0) returns 0 with no conversion error.
ToNumber("Number", 1, 1) returns [Null] and no conversion error.
ToNumber("123456,789", 1, 1, ",") returns 123456.789 as a number.
ToNumber("123.456,789", 1, 1, ",") returns 123 as a number. (This is because conversion stops at the thousands separator, which is not valid for input with the ToNumber function.)
ToString(x, numDec, addThousandsSeparator, decimalSeparator): Converts a numeric parameter to a string using numDec decimal places. By default, the period is used as the decimal separator.
addThousandsSeparator: 0 (default) formats the numeric string without a thousands separator; 1 formats with a thousands separator. By default, the thousands separator is a comma unless "," is specified for decimalSeparator, in which case it is a period.
decimalSeparator: "." (default) specifies the period as decimal separator; "," specifies the comma as decimal separator.
ToString(10, 0) returns 10 as a string.
ToString(10.4, 2) returns 10.40 as a string.
ToString(100.4, 2) returns 100.40 as a string.
ToString(1000.4, 2, 1) returns 1,000.40 as a string.
ToString(123456.789, 3, 1, ",") returns 123.456,789 as a string.
ToString(123456.789, 3, 0, ",") returns 123456,789 as a string.
A DateTime function performs an action or calculation on a date and time value. Use a DateTime function to add or subtract intervals, find the current date, find the first or last day of the month, extract a component of a DateTime value, or convert a value to a different format.
Date support
Designer cannot process dates prior to January 1, 1400.
Alteryx uses the ISO format yyyymmdd HH:MM:SS to represent dates and times. If a DateTime value is not in this format, Alteryx reads it as a string. To convert a column for use and manipulation in the DateTime format, use the DateTimeParse function in the expression editor or the DateTime Tool.
Some DateTime functions require you to set the format for the date. Format strings are comprised of specifiers and separators.
Specifiers always begin with a percent sign (%), followed by a casesensitive letter. The data must include at least a two digit year.
Specifier  Output from DateTimeFormat  Supported Input with DateTimeParse 

%a  Abbreviated weekday name ("Mon")  Any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"), giving an error only if the text given is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date. 
%A  Full weekday name ("Monday")  Day name or any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"), giving an error only if the text given is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date. 
%b  Abbreviated month name ("Sep")  Any valid abbreviation of a month name ("Sep", "SEPT."), giving an error only if the text given is not a name of a month. 
%B  Full month name ("September")  Month name or any valid abbreviation of a month name ("Sep", "SEPT."), giving an error only if the text given is not a name of a month. 
%c  The date and time for the computer’s locale  Not supported 
%C  The century number ("20")  Not supported 
%d  Day of the month ("01")  One or two digits, ignoring spaces ("1" or "01") 
%D  Equivalent to %m/%d/%y  Not supported 
%e  Day of the month, leading 0 replaced by a space (" 1")  One or two digits, ignoring spaces ("1" or "01") 
%h  Same as %b ("Sep")  Any valid abbreviation of a month name ("Sep", "SEPT."), giving an error only if the text given is not a name of a month. 
%H  Hour in 24 hour clock, 00 to 23  Up to two digits for hour, 0 to 23. Not compatible with %p or %P. 
%I
(capital "eye") 
Hour in 12 hour clock, 01 to 12  Up to two digits for hour, 1 to 12. Must follow with %p or %P. 
%j  The day of the year, from 001 to 365 (or 366 in leap years)  3digit day of the year, from 001 to 365 (or 366 in leap years) 
%k  24 hours, leading zero is space, " 0" to "23"  Up to two digits for hour 
%l
(lowercase "ell") 
12 hours, leading zero is space, " 1" to "12"  Not supported 
%M  Minutes, 00 to 59  Up to two digits for minutes 
%m  Month number, 01 to 12  One or two digit month number, 1 or 01 to 12 
%p  "AM" or "PM"  Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12hour format) 
%P  "am" or "pm"  Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12hour format) 
%S  Seconds, 00 to 59  Up to two digits for seconds 
%T  Time in twentyfour hour notation. Equivalent to %H:%M:%S  Not supported 
%u  Day of week as a decimal, 1 to 7, with Monday as 1  Not supported 
%U  This returns the week number, as 00 – 53, with the beginning of weeks as Sunday.  Not supported 
%w  Day of week as a number, 0 to 6, with Sunday as 0  Not supported 
%W  This returns the week number, as 00 – 53, with the beginning of weeks as Monday.  Not supported 
%x  The date for the computer’s locale  Not supported 
%X  The 12hour clock time, including AM or PM (“11:51:02 AM”)  Hours:Minutes:Seconds [AM / PM] 
%y  Last two digits of the year ("16")  Up to four digits are read, stopping at a separator or the end of the string, and mapped to a range of the current year minus 66 to current year plus 33. (For example, in 2016, that's 1950 to 2049.) Limitation with sixdigit dates Because up to four digits are read for the year, formats that are intended to have only two digits without a separator, such as a sixdigit date stamp (for example, %y%m%d for data resembling 170522 for May 22, 2017), are still read as four digits. To work around this limitation, you can:

%Y  All four digits of the year ("2016")  Two or four digits are read. Two digits are mapped to a range of the current year minus 66 to current year plus 33. (For example, in 2016, that's 1950 to 2049.) 
%z  Offset from UTC time (“600”)  Not supported 
%Z  Full timezone name (“Mountain Daylight Time”)  Not supported 
Separators are inserted between DateTime specifiers to form a format string.
Separator  Output from DateTimeFormat  Supported Input with DateTimeParse* 

/  /  / or  
    / or  
space  A space  Any sequence of white space characters 
%n  A newline  Not supported 
%t  A tab  Not supported 
other  Other characters, such as comma, period, and colon  Other characters, such as comma, period, and colon 
* DateTimeParse accepts forward slashes ( / ) and hyphens (  ) interchangeably. However, commas, colons, and all other separators must match the incoming data exactly.
DateTimeAdd(dt,i,u): Adds a specific interval to a DateTime value.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
i: Positive or negative integer of time to add or subtract.
u: DateTime unit, specified between quotes: years, months, days, hours, minutes, or seconds.
DateTimeAdd(DateTimeToday(), 1, "days") returns yesterday’s date.
DateTimeAdd(DateTimeFirstOfMonth(), 1, "months")returns the first of next month.
DateTimeAdd("20160130", 1, "month") returns 20160229 (because February does not have a 30th, but its last day that year is the 29th)
DateTimeAdd("20160330", 1, "month")returns 20160229 (because February does not have a 30th, but its last day that year is the 29th)
 Any fraction in the duration is truncated. For example, you cannot add "1.5 hours". Instead, add "90 minutes".
 Adding a unit does not change the value of smaller units. For example, adding hours does not change the value of minutes or seconds. Adding months does not change the day or time, unless the resulting month would not have such a day. In that case, it goes to the last day of that month.
DateTimeDay(dt): Return the numerical value for the day of the month in a DateTime value.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
DateTimeDay("20170324 11:43:23") returns 24.
DateTimeDiff(dt1,dt2,u): Subtract the second argument from the first and return it as an integer difference. The duration is returned as a number, not a string, in the specified time units.
DateTimeDiff("20160215 00:00:00", "20160115 00:00:01", "Months") returns 1 (because the start and end are the same day of the month)
DateTimeDiff("20120229","20110301","years") returns 0 (even though 20120229 is 365 days after 20110301, February 29 is before March 1st, so “one year” has not yet been completed)
DateTimeDiff("20160214", "20160115", "Months") returns 0 (because the day in February is less than the day in January)
DateTimeDiff("20160214 23:59:59", "20160115 00:00:00", "Months") returns 0 (even though it is only one second short of reaching the required day)
DateTimeDiff('20170228', '20160229', 'Months') returns 11 (even though the 28th is the last day of February in 2017, the 28 is less than 29)
 For Month and Year differences, a month is only counted when the end day reaches the start day, ignoring the time of day.
 For precision of Day, Hour, Minute, and Second, the result is calculated precisely, then fractional parts are truncated, not rounded. Therefore:
 Precision names can be shortened to their first three characters (like ‘sec’ and ‘min’); case is insensitive.
 Be careful when storing time differences in seconds. An Int32 can only hold a difference of 68 years in seconds, or 4082 years in minutes. You can use a Double or an Int64 to hold intervals between all supported dates.
DateTimeDiff(‘20160101 00:59:59’, ‘20160101 00:00:00’, ‘Hours’) is zero.
DateTimeDiff(‘20160101 23:59:59’, ‘20160101 00:00:00’, ‘Days’) is zero.
DateTimeFirstOfMonth(): Returns the first day of the month, at midnight.
DateTimeFormat(dt,f): Convert DateTime data from ISO format to a format for use by another application. Output to a string data type.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
f: The format to which to convert the data, expressed in a format string.
DateTimeFormat([DateTime_Out],"%d%m%Y") returns 22042008 for the date April 22, 2008 (ISO format: 20080422)
DateTimeHour(dt): Return the hour portion of the time in a DateTime value.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
DateTimeHour("20170324 11:43:23") returns 11.
DateTimeHour("20170324") returns 0, as midnight is the assumed hour when no time is specified with a date.
DateTimeLastOfMonth(): Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59).
Designer uses the date and time when the formula is first parsed. In a batch process, this time is used with each new set of data. This allows for consistency if the process takes a long time.
DateTimeMinutes(dt): Return the minutes portion of the time in a DateTime value.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
DateTimeMinutes("20170324 11:43:23") returns 43.
DateTimeMonth(dt): Return the numerical value for the month in a DateTime value.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
DateTimeMonth("20170324 11:43:23") returns 3.
DateTimeMonth("11:43:23") returns [Null], because the incoming data is not valid.
DateTimeNow(): Returns the current system date and time.
DateTimeParse(dt,f): Converts a date string with the specified format to the standard ISO format yyyymmdd HH:MM:SS.
dt: DateTime string data, expressed as a selected field or a DateTime string between quotes. The incoming data must be a String data type, and can be in any format of DateTime as long as this format agrees with the format you specify for the f parameter.
f: The format of the data you are converting, expressed in a format string between quotes.
DateTimeParse("2016/2803","%Y/%d%m") returns 20160328.
DateTimeSeconds(dt): Return the seconds portion of the time in a DateTime value.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
DateTimeSeconds("20170324 11:43:23") returns 23.
DateTimeStart(): Returns the date and time when the current workflow started running.
DateTimeToday(): Returns today’s date.
Expected Behavior: DateTimeToday data type
Despite its name, DateTimeToday() does not return a time value; rather it only return a Date with the current date. You can wrap the DateTimeToday() function in the ToDateTime() function to return a DateTime value with the time set to midnight of the current day.
ToDateTime(DateTimeToday())
DateTimeToLocal(dt): Converts a UTC DateTime to the local system time zone.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
DateTimeToLocal('20140801 20:01:25') returns the local system time zone (Mountain Time) as 20140801 14:01:25
DateTimeToUTC(dt): Converts a DateTime (in local system time zone) to UTC.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
DateTimeToUTC(DateTimeNow()) returns the Coordinated Universal Time at workflow runtime: 20140801 20:01:25 (where Local Mountain time was 20140801 14:01:25)
DateTimeTrim(dt,t): Remove unwanted portions of a DateTime and return the modified DateTime .
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
t: Trim type. Options include:
 firstofmonth: trim to the beginning of the month (this does the same as month)
 lastofmonth: extend to one second before the end of the last day of the month
 year: trim to midnight on January 1st.
 month: trim to midnight at the first day of the month
 day: trim to the day (i.e., midnight). This converts a DateTime to a day with a time of zero (not a date).
 hour: trim to the hour
 minute: trim to the minute.
Trimming a DateTime does not round the returned value. For example, the time 15:59:59 trimmed to the hour becomes 15:00:00, not 16:00:00.
DateTimeTrim("20161207 16:03:00","year") returns 20160101 00:00:00.
DateTimeYear(dt): Return the numerical value for the year in a DateTime value.
dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.
DateTimeYear("20170324 11:43:23") returns 2017.
ToDate(x): Converts a string, number, or DateTime to a Date.
ToDateTime(x): Converts a string, number, or Date to a DateTime.
A file function builds file paths, checks to see if a file exists, or extracts a part of a file path. File functions can only be used with String data types.
FileAddPaths(Path1, Path2): Adds two file path parts, making sure there is exactly one \ (backslash) between the two paths.
FileAddPaths([C:\Temp], [Data\file.csv]) returns "C:\Temp\Data\file.csv"
FileExists(Path): Returns true if the file exists, false if it does not.
FileExists(C:\Temp) returns "True"
FileExists(C:\Temp\Data\file.csv) returns "True"
FileExists(C:\Temp\Data\NoFile.csv) returns "False"
FileGetDir(Path): Returns the directory portion of the path.
FileGetDir(C:\Temp\Data\file.csv) returns C:\Temp\Data
FileGetDir(C:\Temp\Data) returns C:\Temp
FileGetExt(Path): Returns the extension of the path, including the . (period).
FileGetExt(C:\Temp\Data\file.csv) returns ".csv"
FileGetExt(C:\Temp\Data) returns " "
FileGetFileName(Path): Returns the name portion of the path, without the extension.
FileGetName(C:\Temp\Data\file.csv) returns "file"
FileGetName(C:\Temp\Data) returns "Data"
A finance function applies financial algorithms or mathematical calculations.
FinanceCAGR(BeginningValue, EndingValue, NumYears): Compound Annual Growth Rate: The geometric mean growth rate on an annualized basis.
FinanceEffectiveRate(NominalRate, PaymentsPerYear): Effective Annual Interest Rate: The interest rate on a loan or financial product restated from the nominal interest rate as an interest rate with annual compound interest payable in arrears
FinanceFV(Rate, NumPayments, PaymentAmount, PresentValue, PayAtPeriodBegin): Future Value of an Investment: The value of an asset at a specified time in the future assuming a certain interest rate or rate of return.
FinanceFVSchedule(Principle, Year1Rate, Year2Rate): Future Value Schedule: The future value of an initial principal after applying a series of interest rates to an investment.
FinanceIRR(Value1, Value2): Internal Rate of Return: The interest rate at which the costs of the investment lead to the benefits of the investment. This means that all gains from the investment are inherent to the time value of money and that the investment has a zero net present value at this interest rate.
FinanceMIRR(FinanceRate, ReinvestRate, Value1, Value2): Modified Internal Rate of Return: A modification of the internal rate of return that aims to resolve some problems with the IRR. The MIRR is a financial measure of an investment's attractiveness.
FinanceMXIRR(FinanceRate, ReinvestRate, Value1, Date1, Value2, Date2): Modified Internal Rate of Return of an investment with dates
FinanceNominalRate(EffectiveRate, PaymentsPerYear): Nominal Annual Interest Rate: An interest rate is called nominal if the frequency of compounding (such as a month) is not identical to the basic time unit (normally a year).
FinanceNPER(Rate, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Number of periods for an investment or loan
FinanceNPV(Rate, Value1, Value2): Net Present Value of an investment: Measures the excess or shortfall of cash flows, in present value terms, once financing charges are met.
FinancePMT(Rate, NumPayments, PresentValue, FutureValue, PayAtPeriodBegin): Calculates payments on a loan
FinancePV(Rate, NumPayments, PaymentAmount, FutureValue, PayAtPeriodBegin): Present Value of an Investment: The value on a given date of a future payment or series of future payments, discounted to reflect the time value of money and other factors such as investment risk.
FinanceRate(NumPayments, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Returns the interest rate (per period)
FinanceXIRR(Value1, Date1, Value2, Date2): Internal Rate of Return of an investment with dates
FinanceXNPV(Rate, Value1, Date1, Value2, Date2): Net Present Value of an investment with dates
A math function performs mathematical calculations. Math functions can only be used with Number data types.
ABS(x): Absolute value of (x). It is the distance between 0 and a number. The value is always positive.
ABS(32) returns 32
ABS(32) returns 32
Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is (x). The returned angle is given in radians in the range 0 (zero) to pi. The number (x) must be between 1 and 1.
ACOS(0.5) returns 1.047198
Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is (x). The returned angle is given in radians in the range pi/2 to pi/2.
ASIN(0.5) returns 0.523599
Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is (x). The returned angle is given in radians in the range pi/2 to pi/2.
ATAN(0.8) returns 0.674741
ATAN2(y, x): Arc  Tangent of (y/x)
Returns the arctangent of y and x.
ATAN2(4, 3) returns 2.2143
Average(n1,...): Average of a list of numbers.
Average(20,30,55) returns 35 since [(20+30+55)/3]= 35
CEIL(x): Returns smallest integer greater than or equal to (x). Works like the RoundUp function in Excel.
CEIL(1.1) returns 2
CEIL (6.54) returns 7
CEIL(30.42) returns 30
COSH(x) : Hyperbolic Cosine of (x)
DISTANCE(from_Lat,from_Lon, to_Lat, to_Lon): Returns distance from (lat1,Lon1) to (lat2,lon2)
DISTANCE(42,90, 43, 80) returns a value of 513.473706 miles
FLOOR(x): Returns largest integer less than or equal to (x)
FLOOR(1.1) returns 1
FLOOR(6.54) returns 6
FLOOR(30.42) returns 31
LOG(x): Natural logarithm of (x)
LOG(1) returns 0
LOG(14) returns 2.639057
LOG10(x): Base 10 logarithm of (x)
LOG10(1) returns 0
LOG10(14) returns 1.146128
Median(...): Calculates the median out of one or more (possibly unsorted) values.
Median (5, 4, 3, 7, 6) returns 5
Mod(n,d): Modulo of n divided by d (integer operation)
The Modulo operation finds the remainder of one integer (n) divided by a divisor integer (d). If (n) is positive, the result is positive. If (n) is negative, the result is negative.
If decimal values are used, the decimal is truncated. This can result in a "Divided by 0" error if the divisor is a decimal less than 1.
MOD(6,4) returns 2
MOD(6,4) returns 2
MOD(6,4) returns 2
MOD(6,4) returns 2
MOD(6,.4) returns [Null]
PI(): Returns the value of the constant PI to 15 digits of accuracy
PI() returns 3.141593
POW(x, e): Returns (x) raised to the (e) power
POW(2, 3) is equivalent to 2 ^{3} and returns 8
RAND(): Returns a random number between 0 and 1
RAND() returns a random number like 0.256
RandInt(n): Returns a random integer between 0 and the specified parameter
RandInt(10) returns 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 or 10
RandInt(5000) returns a random whole number between 0 and 5000, such as 741
Round(x, mult): Returns (x) rounded to nearest multiple of (mult)
Round(55.34, 2) returns a value of 56
Round(39.88, 10) returns a value of 40
Round(41.1, 10) returns a value of 40
Round(1.25, 0.1) returns a value of 1.3
Round(1.227, 0.01) returns a value of 1.23
SINH(x): Hyperbolic Sine of (x)
SmartRound(x): Returns (x) rounded to nearest multiple of a value determined dynamically based on the size of (x)
SQRT(100) returns a value of 10
A bitwise function operates on one or more bit patterns or binary numerals at the level of their individual bits. Use a bitwise function to manipulate values for comparisons and calculations. Bitwise functions can only be used with Number data types.
BinaryAnd(n,m): Returns a binary of n and m. The result is 1 if both n and m are 1, and 0 otherwise. If 0 is equated with false, and 1 with true the BinaryAnd operation works like a logical And.
BinaryAnd(1,1) returns 1
BinaryAnd(1,0) returns 0
BinaryNot(n): Returns a Binary Not of n
BinaryNot(6) returns 7
BinaryNot(2) returns 3
BinaryOr(n,m): Returns a Binary Or of n and m
BinaryOr(6,6) returns 6
BinaryOr(6,2) returns 6
BinaryXOr(n,m): Returns a Binary XOr of n and m
BinaryXOr(6,6) returns 0
BinaryXOr(6,2) returns 4
A minimum or maximum function finds the smallest and largest value of a set of values. Min/Max functions can only be used with Number data types.
Bound(x, min, max): If (x < min) return min; else if (x > max) return max; else return x
Bound(6,1,5) returns 5
Bound(3,1,5) returns 3
Max(v0, v1,..., vn): Returns the maximum value from the list.
Max(15,180,7,13,45,2,13) returns 180
MaxIDX(v0, v1,..., vn): Returns the 0 based index of the maximum value from the list.
MaxIDX(15,180,7,13,45,2,13) returns 1
Min(v0, v1,..., vn): Returns the minimum value from the list.
Min(15,180,7,13,45,2,13) returns 2
MinIDX(v0, v1,..., vn): Returns the 0 based index of the minimum value from the list.
MinIDX(15,180,7,13,45,2,13) returns 5
An operator is a character that represents an action. Use an arithmetic operator to perform mathematical calculations or a Boolean operator to work with true/false values. Operators can be used with all data types.
/*Comment*/: Block Comment: Allows you to add comments to an expression editor (within the expression line) without interfering with the expression.
//Comment: Single Line Comment: Allows you to add comments to an expression editor without interfering with the expression.
Boolean AND &&: Boolean AND: Used to combine two Boolean values. The result is also a Boolean value. The result is true if both of the combined values are true, and the result is false if either of the combined values is false.
Boolean AND  Keyword: Boolean AND: Used to combine two Boolean values. The result is also a Boolean value. The result is true if both of the combined values are true, and the result is false if either of the combined values is false.
Boolean NOT !: Boolean NOT: Accepts one input. If that input is TRUE, it returns FALSE. If that input is FALSE, it returns TRUE.
Boolean NOT  Keyword: Boolean NOT: Accepts one input. If that input is TRUE, it returns FALSE. If that input is FALSE, it returns TRUE.
Boolean OR  Keyword: Boolean OR: If either (or both) of the two values it checks are TRUE, then it returns TRUE.
Boolean OR : Boolean OR: If either (or both) of the two values it checks are TRUE, then it returns TRUE.
Close Parenthesis ): Close Parenthesis
Greater Than Or Equal >=: Greater Than Or Equal
Less Than Or Equal <=: Less Than Or Equal
Multiplication *: Multiplication
Open Parenthesis (: Open Parenthesis
value IN (...)  Keyword: Test Value in List: Determines if a given value matches any value in a subquery or a list.
value NOT IN (...)  Keyword: Test Value Not in List: Determines if a given value matches any value not in a subquery or a list.
A spatial function builds spatial objects, analyze spatial data, and returns metrics from spatial fields. A spatial function can only be used with Spatial data types.
Spatial Functions are consistent with the Open GIS Consortium, Inc. For more information see http://portal.opengeospatial.org/files/?artifact_id=829.
Spatial objects in formulas can use operators: plus +; minus ; equals ==; not equal !=
ST_Area(object, units): Returns the area in the specified units of the spatial object. (numeric data value)
ST_Boundary(object): Returns the boundary of the spatial object. (polyline spatial object indicating the boundary of the input polygon)
ST_BoundingRectangle(object, ...): Returns the bounding rectangle of the spatial object. (polygon spatial object)
ST_Centroid(object): Returns the centroid of the spatial object. (point spatial object)
ST_CentroidX(object): Returns the longitude of the centroid of the spatial object (numeric data value)
ST_CentroidY(object): Returns the latitude of the centroid of the spatial object (numeric data value)
ST_Combine(object1, object2,...): Combines the spatial objects. (spatial object)
ST_Contains(object1,object2): Returns True if object1 contains object2. (Boolean value)
ST_ConvexHull(object1,...): Returns the convex hull of the object(s). (spatial object)
ST_CreateLine(point1, point2...): Creates a line by connecting the specified points and lines in a sequence. (spatial object)
ST_CreatePoint(x,y): Returns a spatial object containing the specified longitude and latitude coordinates. (spatial object)
ST_CreatePolygon(obj1, obj2...): Creates a polygon by connecting the specified points and lines in a sequence. (spatial object)
ST_Cut(object1,object2): Returns the result of cutting object1 from object 2. (spatial object)
ST_Dimension(object): Returns the dimension of the spatial object. The spatial dimension is the minimum number of coordinates needed to specify every point that makes up the spatial object. Point objects return a value of 0, Line objects return a value of 1, and Polygon objects return a value of 2. (numeric data value)
ST_Distance(object1, object2, units): Returns the distance in specified units from object1 to object2. Supported units are Miles (Mi), Kilometers (KM), Meters, Feet. (numeric data value)
ST_EndPoint(object): Returns the last point of the spatial object. (point spatial object)
ST_Intersection(object1, object2,...): Returns the intersection of the specified spatial objects. (spatial object)
ST_Intersects(object1, object2...): Returns True if the spatial objects intersect. (Boolean value)
ST_InverseIntersection(object1, object2...): Returns the inverse intersection of the specified spatial objects. (spatial object)
ST_Length(object, units): Returns the linear length of the spatial object. (numeric data value)
ST_MaxX(object): Returns the maximum longitude of the spatial object. (numeric data value)
ST_MaxY(object): Returns the maximum latitude of the spatial object. (numeric data value)
ST_MinX(object): Returns the minimum longitude of the spatial object. (numeric data value)
ST_MinY(object): Returns the minimum latitude of the spatial object. (numeric data value)
ST_NumParts(object): Returns the number of parts in the spatial object. (numeric data value)
ST_NumPoints(object): Returns the number of points in the spatial object. (numeric data value)
ST_ObjectType(object): Returns the spatial object type as a string value. (string data value)
ST_PointN(object, n): Returns the Nth point in the spatial object. (point spatial object)
ST_RandomPoint(object): Returns a random point within the spatial object. (point spatial object)
ST_Relate(object1,object2,relation): Returns True if the objects satisfy the provided DE9IM relation. (Boolean value)
ST_StartPoint(object): Returns the first point in the spatial object. (spatial object)
ST_Touches(object1, object2): Returns True if object 1 touches object 2. (Boolean value)
ST_TouchesOrIntersects(object1, object2): Returns True if object 1 touches or Intersects object 2. (Boolean value)
ST_Within(object1, object2): Returns True if object 1 is contained by object 2. (Boolean value)
These functions perform a variety of specialized actions and can be used with all data types.
EscapeXMLMetacharacters(String): Replaces XML metacharacters with their escaped versions. There are five characters that will be escaped.
Character  Escaped Version 

"  " 
'  ' 
<  < 
>  > 
&  & 
EscapeXMLMetacharacters("2>1&1<2") returns 2>1&1<2
GetVal(index, v0,...vn): Returns the value (v0,..., vn) specified by the 0based [index].
GetEnvironmentVariable(Name): Returns the environment variable specified in Name. To get a list of environment variables, go to Control Panel > System > Advanced System Settings > Environment Variables. A list of system variables appears. Any value from this can be used.
Gallery support
The GetEnvironmentVariable is not supported in apps saved to the Gallery.
GetEnvironmentVariable(CommonProgramFiles) returns C:\Program Files\Common Files
GetEnvironmentVariable(OS) returns Windows_NT
Message(messageType, message, returnValue): Use with a conditional statement to output a message to the message log and update column data with a specified value when a condition is met.
The message type and text is shown in the Results window. If logging is enabled, the output log file also resembles this information. See Results Window and Output Log File.
messageType: A number that corresponds to the type of message:
 1 = Messages
 2 = Warnings
 3 = Errors
 5 = Conv Errors (field conversion errors)
 8 = Files (input)
 9 = Files (output)
message: The text of the message, expressed as a string between quotes.
returnValue: A value to output to column data. This can be a numeric value (for example, 0), null, or a text string between quotes (for example, "False").
In this example, the Message function is used within a conditional expression to output a field conversion error message and update column data with a value of "False" if given date/time values do not contain valid time data.
If [Hour] = "0" and [Minutes] = "0" then
Message(5, "Invalid time in DateTime", "False")
Else
"True"
Endif
RangeMedian(...): Calculates the median from a series of aggregated ranges. See Range Median.
ReadRegistryString(Key, ValueName, DefaultValue="): Returns a value from the registry.
ReadRegistryString('HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SRC\Alteryx', 'InstallDir64') returns C:\Program Files\Alteryx\bin
Soundex(string): Returns the Soundex of the String. Soundex creates a code based on the first character in the string plus three characters based on the following:
Character  Soundex 

Nonalpha characters (numbers and punctuation)  1 
a, e, i, o, u, y, h and w  ignored unless the first character in the string 
b, f, p and v  1 
c, g, j, k, q, s, x and z  2 
d and t  3 
l  4 
m and n  5 
r  6 
If the resulting code is only 2 or 3 characters long, Soundex uses zeros to fill out the code to four characters. For example, in the name Lauren, only the L, r, and n are translated (Lrn), so the resulting Soundex code becomes L650.If the resulting code is more than four characters long, all characters after the fourth character are ignored. For example, in the name Patrick, the P, t, r, c, and k can be translated (Ptrck), but the resulting Soundex code will only be four characters: P362.
Soundex_Digits(string): Returns the first four digits or the Soundex if none. If there are digits (numbers) in the String, the first four digits are returned. If there are no digits, the Soundex code is returned.
String  Soundex_Digits 

3825 Iris  3825 
55555 Main  5555 
14L Broadway  14 
Commerce Street  C562 
L Street  L236 
TOPNIDX(N, v0, v1, ..., vn): Returns the 0 based index of the Nth from the maximum value from the list. With N==0, it is the same as MaxIdx(...)
N is the starting point, v0 is the indexed field (subsequent Variables are optional), and vn is the goal.
TOPNIDX(0, [IndexedField], 5) returns the top five of a stream
UrlEncode(String): Encodes string as a web legal URL
C:\temp\Alteryx URL Encode returns C:/temp/Alteryx%20URL%20Encode
A string function performs operations on text data. Use a string function to cleanse data, convert data to a different format or case, compute metrics about the data, or perform other manipulations. A string function can only be used with String data types.
Contains(String, Target, CaseInsensitive=1): Searches for the occurrence of a particular string within a string. Returns a TRUE or FALSE if the string matches the pattern. It is defaulted to case insensitive.
The CONTAINS function is case insensitive while the FINDSTRING function is case sensitive.
Contains('123ABC', 'ABC') returns TRUE
Contains('123ABC', 'abc') returns TRUE
Contains('123ABC', 'abc', 0) returns FALSE
CountWords(String): Returns the count of words in the specified String. Words are defined by characters separated by a space.
CountWords("Basic Variables Households") returns 3
CountWords("Basic Variables Age:Female (Pop)Age 1") returns 5
DecomposeUnicodeForMatch(String): Removes accents and expands compound characters while converting to narrow. This function takes a Unicode® string and translates it to a lower case, narrow character string. All accents and other decorations will be removed.
This function is useful for matching only. It is not considered a normalized string.
DecomposeUnicodeForMatch("Prénoms français") returns prenoms francais
EndsWith(String, Target, CaseInsensitive=1): Checks if a string ends with a particular string. Returns a TRUE or FALSE if the string matches the pattern. It is defaulted to case insensitive.
EndsWith('123ABC', 'ABC') returns TRUE
EndsWith('123ABC', 'abc') returns TRUE
EndsWith('123ABC', 'abc', 0) returns FALSE
The EndsWith function is case insensitive while the FindString function is case sensitive.
FindString(String,Target): Searches for the occurrence of a particular string (Target) within another string (String) and returns the numeric position of its occurrence in the string.
Returns the 0 based index of the first occurrence of Target in String. Returns 1 if no occurrence.
FindString([Name], "John") returns 0 when the string contains John and returns 1 when the string does not.
IF (FINDSTRING([Name], "John") =0) THEN "John Smith" ELSE "Other" ENDIF returns John Smith when the string contains John and returns Other when the string does not.
GetWord(String, n): Returns the Nth (0based) word in the String. Words are defined as a collection of characters separated by a space. 0based index, means the first word is at the 0 position.
GetWord("Basic Variables Households", 0) returns "Basic"
GetWord("Basic Variables Households", 1) returns "Variables"
Left(x, len): Returns the first [len] characters of the string (x). If len is less than 0 or greater than the length of x, x remains unchanged.
Left("92688", 3) returns a value of "926"
Length(x) : Returns the length the string (x).
Length("92688") returns a value of 5
LowerCase(x): Converts a string to lower case
LowerCase("M1P 1G6") returns "m1p 1g6"
MD5_ASCII(String): Calculates the MD5 hash of the string. The string is expected to be only ASCII characters. Unicode® characters are turned into ? before calculating the MD5 hash.
MD5_UNICODE(String): Calculates the MD5 hash of the string.
PadLeft (str, len, char): Pads the string to the left with the specified character to the specified length. If the padding “char” is more than one character long, only the first character will be used.
PadLeft("M", 4, "x") returns "xxxM"
PadRight (str, len, char): Pads the string to the right with the specified character to the specified length. If the padding “char” is more than one character long, only the first character will be used.
PadRight("M", 4, "x") returns "Mxxx"
REGEX_CountMatches(string,pattern,icase): Returns the count of matches within the string to the pattern.
The icase is an optional parameter. When specified, the case must match. By default icase=1 meaning ignore case. If set to 0, the case must match.
Consult the Boost Regex Perl Regular Expression Syntax page to make the building of the expression easier.
REGEX_Match(string,pattern,icase): Searches a string for an occurrence of a regular expression.
Tells if the string matches the pattern from the first character to the end. To look for something that does not necessarily start at the beginning of the string, start the pattern with '.*'. To look for something that does not necessarily go all the way to the end of the string, end the pattern with '.*'.
Consult the Boost Regex Perl Regular Expression Syntax page for more information on how to properly construct a regular expression.
icase is an optional parameter. When specified, the case must match. By default icase=1 meaning ignore case. If set to 0, the case must match.
REGEX_Match(123456789, "\d{3}\d{2}\d{4}") returns 1 (True)
REGEX_Replace(string, pattern, replace,icase): Allows replacement of text using regular expressions and returns the string resulting from the RegEx find pattern and replace string. All occurrences of the match are replaced, not just the first.
Consult the Boost Regex Perl Regular Expression Syntax page to make the building of the expression easier. The replace parameter can be either a specified value as shown below, or a marked group, such as "$1"
icase is an optional parameter. When specified, the case must match. By default icase=1 meaning ignore case. If set to 0, the case must match.
REGEX_Replace("Don't reveal your Social Security number, 123456789","\d{3}\d{2}\d{4}", "CLASSIFIED") returns Don't reveal your Social Security number, CLASSIFIED
REGEX_Replace("Change all domain names from alteryx@Alteryx.com","@.*\.", "@extendthereach.") returns Change all domain names from alteryx@extendthereach.com
Replace(Str, Target, Replacement): Returns the string (Str) after replacing each occurrence of the String (Target) with the String (Replacement).
Replace("Good judgment comes from experience", "experience", "awareness") returns "Good judgement comes from awareness"
ReplaceChar(x, y, z): Returns the string (x) after replacing each occurrence of the character (y) with the character(z). If the replacement character (z) is a string with more than one character, only the first one is used. If (z) is empty, each character (x) that matches any character in (y) is simply removed.
ReplaceChar("abcdefb", "b", "_") returns "a_cdef_"
ReplaceChar("@a#b%c", "@,#,%", "_") returns "_a_b_c"
ReplaceFirst(Str, Target, Replacement): Returns the string (Str) after replacing the first occurrence of the string (Target) with the string (Replacement).
ReplaceFirst("abcdefb", "b", "_") returns "a_cdefb"
ReverseString(Str): Reverses all the characters in the string.
ReverseString("abcdefb") returns "bfedcba"
Right(String, len): Returns the last (len) characters of the string. If len is less than 0 or greater than the length of String, the string remains unchanged.
Right("92688", 3) returns a value of "688"
StartsWith(String, Target, CaseInsensitive=1): Checks if a string starts with a particular string. Returns a TRUE or FALSE if the string matches the pattern. It is defaulted to case insensitive.
The StartsWith function is case insensitive while the FindString function is case sensitive.
StartsWith('ABC123', 'ABC') returns TRUE
StartsWith('ABC123', 'abc') returns TRUE
StartsWith('ABC123', 'abc', 0) returns FALSE
STRCSPN(x, y): Returns the length of the initial segment of the string (x) consisting of characters NOT in the string (y)
STRCSPN("Bob's Amaco", "~!@#$%^&*'()") returns 3. This is a useful test to make sure there is no punctuation in the string.
StripQuotes(x): Removes a matched set of quotation marks or apostrophes from the ends of the string
StripQuotes("Hello there") returns Hello there
StripQuotes("'Hello there,' she said.") returns 'Hello there,' she said.
StripQuotes('"Hello there," she said.') returns "Hello there," she said.
STRSPN(x, y): Returns the length of the initial segment of the string [x] consisting of characters in the string [y]
STRSPN("3034408896x105", "0123456789") returns 10. This is a useful test to make sure a string consists of a set of characters.
Substring(x, start, length): Returns the substring of (x) starting at (start), stopping after(length), if provided
Substring("9492224356", 4, 8) returns "2224356"
Substring("9492224356", 4, 6) returns "22243"
Substring("9492224356", 4) returns "2224356"
TitleCase(x) : Converts a string to Title case
TitleCase("john smith") returns "John Smith"
Trim(x, y): Removes the character(s) in the string y from the ends of the string x. Y is optional and defaults to trimming white space. Notice in the TRIM function examples the specified characters are trimmed. It doesn't matter what order the characters are in.
Trim("!see instructions!!!", "!") returns "see instructions"
Trim(" Test123 ") returns "Test123"
TrimLeft(x, y): Removes character in the string y from the beginning of the string x. Y is optional and defaults to trimming white space
TrimLeft("** special invitation ", " *") returns "special invitation "
TrimRight(x, y): Removes character in the string y from the end of the string x. Y is optional and defaults to trimming white space
TrimRight("John Smith ") returns "John Smith"
TrimRight("John Smith**","*") returns "John Smith"
While you may be passing in a string of characters to trim, the TRIM functions do not respect the order of the characters; it treats the string as a "list." Therefore it will trim ALL of the characters in the list. If you are looking to replace a string, use a REPLACE function or a REGEX function in your expression.
Converts a string to upper case
About Length and Strings: When referencing the LENGTH of a string the first character is counted as 1 (the length of the following string "record" is 6).
HOWEVER, when referencing the character position within a STRING, positions are actually counted BETWEEN characters. So think of the first character's position as 0. Therefore, the position of the letter "c" in the string "record" is at position 2.
Uppercase("John Smith") returns "JOHN SMITH"
UuidCreate(): Creates a Unique identifier.
UuidCreate() returns a unique value such as ba1318361ba34d428f7eb81a99c2e838
A test function performs validation tests on data. Use a test function to identify the data type of a value or determine if a value exists. A test function can be used on all data types.
CompareDictionary(a,b): Compares two strings in dictionary order. Returns 1 if a<b, 0 if a==b, 1 if a>b
CompareDictionary (apples, bananas) returns 1
CompareDictionary (cherries, bananas) returns 1
CompareDictionary (bananas, bananas) returns 0
CompareDigits(a,b, nNumDigits): Compares two numbers and determines if they are the same to the given number of digits. The comparison finds the difference between the numbers and reports A and B the same when the leading digit of the difference is NumDigits or more places to the right of the digits of the larger of A and B.
The NumDigits argument must not be null, and must be between 1 and 19, otherwise an error will result. If a noninteger is given, it is rounded to the nearest integer. (NumDigits can be between 0.5 and 19.499)
CompareDigits(12345, 12444, 3) returns "True" (because the difference is 99, and its leading digit is 3 places to the right of the leading digit of A)
CompareDigits(12345, 123445, 3) returns "False" (because the difference is 100, and its leading digit is only 2 places to the right of the leading digit of A)
CompareDigits(12.345, 12.347, 3) returns "True" (because the difference is 0.002, and its leading digit is 4 places to the right of the leading digit of A)
CompareDigits(12.345, 12.435, 3) returns "True" (because the difference is 0.09, and its leading digit is 3 places to the right of the leading digit of A)
CompareDigits( .91234, .91334, 3) returns “False” (because the difference is .001, and its leading digit is only 2 places to the right of the leading digit of A)
CompareEpsilon(a,b,epsilon): Compares two floating point numbers and returns true if they are within epsilon.
CompareEpsilon([123.456789101112], [123.456789101114], 0.0001) returns "True"
CompareEpsilon([123.456], [123.456789101112],0.0001) returns "False"
IsEmpty(v): Tests if v is NULL or equal to "" or not
Name  IsEmpty 

John  False 
True  
Mary  False 
[Null]  True 
IsInteger(v) : Tests if v contains a value that can be converted to an integer. If so, it will return true.
Value  IsInteger 

1  True 
1.23  False 
B  False 
IsNull(v) : Tests if v is NULL or not
To populate a record with a NULL value, use the function NULL()
Name  IsNull 

John  False 
[Null]  True 
Mary  False 
IsNumber(v) : Tests if the field type for v is a number or not.
IsSpatialObj(v): Tests if the field type for v is a spatial object or not.
IsString(v): Tests if the field type for v is a string or not.