Skip to main content
Documentation
Documentation
Workday User Guide
Last Updated: 2024-06-28
Prism Expression Quick Reference

Prism Expression Quick Reference

Conversion Functions

Function
Description
Example
Constructs a Currency field from a
NUMERIC
 value and a
TEXT
 or
INSTANCE
 value that contains a valid currency code. When the currency code isn't valid, this function returns NULL.
BUILD_CURRENCY([Sale Price], [Currency Code])
Converts data values from one field type (data type) to another.
CAST([Region] AS Instance(eecb565181284b6a8ae8b45dc3ed1451))
CAST(99.99 AS decimal(10,3))
 returns
99.990
.
CAST(99.99 AS decimal(20,2))
 returns
99.99
.
CAST(99.999 AS decimal(10,2))
 returns
100.00
.
Converts
LONG
 values to
DATE
 values, where the input number represents the number of milliseconds since the epoch.
EPOCH_MS_TO_DATE(1360260240000) returns
2013-02-07T18:04:00:000Z
Takes a Currency value and extracts the numeric amount as a
NUMERIC
 value.
EXTRACT_AMOUNT([Salary])
Takes a
CURRENCY
 value and extracts the currency code as an
INSTANCE
 value.
EXTRACT_CODE([Salary])
Takes a
CURRENCY
 value and extracts the currency code as a
TEXT
value.
EXTRACT_CODE_TEXT([Salary])
Converts
TEXT
,
BOOLEAN
,
INTEGER
,
LONG
, or
NUMERIC
 values to
BOOLEAN
.
TO_BOOLEAN([is_contingent])
Converts
TEXT
 values that contain valid currency-formatted data to
CURRENCY
 values.
TO_CURRENCY("1234.56 USD")
TO_CURRENCY(CONCAT(TO_STRING([Grant Price]), [Grant Code]))
Converts
TEXT
 values to
DATE
 values, and specifies the format of the date and time elements in the string.
TO_DATE([order_date],"yyyy.MM.dd 'at' HH:mm:ss z")
Converts
TEXT
,
BOOLEAN
,
INTEGER
,
LONG
,
DOUBLE
, or
NUMERIC
 values to
NUMERIC
 values with the default number of digits before and after the decimal point.
TO_DECIMAL([average_rating])
Converts
TEXT
,
BOOLEAN
,
INTEGER
,
LONG
, or
DOUBLE
 values to
DOUBLE
 (a type of numeric) values.
TO_DOUBLE([average_rating])
Converts
TEXT
,
BOOLEAN
,
INTEGER
,
LONG
, or
DOUBLE
 values to
INTEGER
 (whole number) values. When converting
DOUBLE
 values, everything after the decimal will be truncated (not rounded up or down).
TO_INT([average_rating])
Converts
TEXT
,
BOOLEAN
,
INTEGER
,
LONG
,
DECIMAL
,
DATE
, or
DOUBLE
 values to
Long
 (whole number) values. When converting
Decimal
 or
DOUBLE
 values, everything after the decimal will be truncated (not rounded up or down).
TO_LONG([average_rating])
Converts values of other data types to
TEXT
 (character) values.
TO_STRING([sku_number])

Date Functions

Function
Description
Example
Adds the specified time interval to a
DATE
 value.
DATE_ADD([invoice_date], 45, "day")
Calculates time between 2
DATE
 values (
value1
 -
value2
) and truncates it to days (accounting for daylight savings).
DAYS_BETWEEN([ship_date], [order_date])
Returns the specified portion of a
DATE
 value.
EXTRACT("hour",[order_date])
Calculates the whole number of hours (ignoring minutes, seconds, and milliseconds) between two
DATE
values (
value1
 -
value2
).
HOURS_BETWEEN([ship_date],[order_date])
Calculates the whole number of milliseconds between two
DATE
 values (
value1
 -
value2
).
MILLISECONDS_BETWEEN([request_timestamp], [response_timestamp])
Calculates the whole number of minutes (ignoring seconds and milliseconds) between two
DATE
values (
value1
 -
value2
).
MINUTES_BETWEEN([impression_timestamp], [conversion_timestamp])
Calculates the whole number of seconds (ignoring milliseconds) between two
DATE
 values (
value1
-
value2
).
SECONDS_BETWEEN([impression_timestamp], [conversion_timestamp])
Returns the current system date and time as a
DATE
 value (no time information). It can be used in other expressions involving
DATE
 type fields, such as
YEAR_DIFF
. Note that the value of
TODAY
 is only evaluated at the time a dataset is published (it is not re-evaluated with each query).
YEAR_DIFF(TODAY(), [birthdate])
Truncates a
DATE
 value to the specified format.
TRUNC(TO_DATE([order_date], "MM/dd/yyyy HH:mm:ss"), "day")
Calculates the fractional number of years between two
DATE
 values (
value1
 -
value2
).
YEAR_DIFF(TODAY(), [birthdate])

Informational Functions

Function
Description
Example
Returns
0
 if the returned value is
NULL
, and
1
 if the returned value is
NOT NULL
. This is useful for computing other calculations where you want to exclude NULL values (such as when computing averages).
IS_VALID([sale_amount])

Instance Functions

Function
Description
Example
Constructs a Multi-Instance field from one or more provided Multi-Instance or Instance fields.
CREATE_MULTI_INSTANCE([Journal1], [Journal2], [Journal3])
Compares a Multi-Instance or Instance field to either a Multi-Instance field, an Instance field, or to a list of instance values, and returns True if at least one instance value exists in the first argument, and False if none of them exist.
INSTANCE_CONTAINS_ANY([Worktags], [Cost Center 1], [Cost Center 2])
Returns the total number of instance values in a Multi-Instance or Instance field. This function returns 0 when the field is empty.
INSTANCE_COUNT([Journal Lines])
Compares a Multi-Instance or Instance field to either a Multi-Instance field, an Instance field, or to a list of instance values, and checks if the first argument exactly matches the instance values provided in the other arguments.
INSTANCE_EQUALS([Worktags], [Cost Center 1], [Cost Center 2])
Compares a Multi-Instance field to either a Multi-Instance field, an Instance field, or to a list of instance values, and returns True if every instance value exists in the first argument, and False if at least one doesn't exist.
INSTANCE_IS_SUPERSET_OF([Worktags], [Cost Center 1], [Cost Center 2])

Logical Functions

Function
Description
Example
Evaluates each row in the dataset according to one or more input conditions, and outputs the specified result when the input conditions are met.
CASE WHEN [gender] = "M" THEN "Male" WHEN [gender] = "F" THEN "Female" ELSE "Unknown" END
Returns the first valid value (
NOT NULL
 value) from a comma-separated list of expressions.
COALESCE([hourly_wage] * 40 * 52, [salary])

Math Functions

Function
Description
Example
DIV
Divides two
LONG
 values and returns a quotient value of type
LONG
 (the result is truncated to 0 decimal places).
DIV(TO_LONG([file_size]), 1024)
EXP
Raises the mathematical constant
e
 to the power (exponent) of a numeric value and returns a value of type
DOUBLE
.
EXP([Value])
Returns the largest integer that is less than or equal to the input argument.
FLOOR(32.6789) returns
32.0
Evenly partitions data values into the specified number of buckets. It creates a hash of the input value and assigns that value a bucket number. Equal values will always hash to the same bucket number.
HASH([username],20)
LN
Returns the natural logarithm of a number. The natural logarithm is the logarithm to the base
e
, where
e
 (Euler's number) is a mathematical constant approximately equal to 2.718281828. The natural logarithm of a number
x
 is the power to which the constant
e
 must be raised in order to equal
x
.
LN(2.718281828) returns
1
MOD
Divides 2
LONG
 or
INTEGER
 values and returns the remainder value of type
LONG
 or
INTEGER
 (the result is truncated to 0 decimal places).
MOD(TO_LONG([file_size]), 1024)
POW
Raises the a numeric value to the power (exponent) of another numeric value and returns a value of type
DOUBLE
.
100 * POW([end_value]/[start_value], 0.2) - 1
Rounds a numeric value to the specified number of decimal places and returns a value of type
DOUBLE
.
ROUND(32.4678954,2) returns
32.47

Text Functions

Function
Description
Example
Compares two
TEXT
 arguments representing a CIDR mask and an IP address, and returns
1
 if the IP address falls within the specified subnet mask or
0
 if it does not.
CIDR_MATCH("60.145.56.0/24", "60.145.56.246") returns
1
Returns a
TEXT
 by concatenating (combining together) the results of multiple
TEXT
 expressions.
CONCAT([month], "/", [day], "/", [year])
Extracts the value of the given cookie identifier from a semi-colon delimited list of cookie key/value pairs. This function can be used to extract a particular cookie value from a combined web access log Cookie column.
EXTRACT_COOKIE("SSID=ABC; vID=44", "vID") returns
44
Extracts the value for the given key from a string containing delimited key/value pairs.
EXTRACT_VALUE("firstname;daria|lastname;hutch", "lastname", ";", "|") returns
hutch
Returns the original file name from the source file system when the data comes from a base dataset. If you're new to Workday, you don't have access to create or edit base datasets.
TO_DATE(SUBSTRING(FILE_NAME(), 0, 8), "yyyyMMdd")
Converts a hexadecimal-encoded
TEXT
 value to a text representation of an IP address.
HEX_TO_IP(AB20FE01) returns
171.32.254.1
Returns an integer indicating the position of a character within a string that is the first character of the occurrence of a substring. The
INSTR
 function is similar to the FIND function in Excel, except that the first letter is position 0 and the order of the arguments is reversed.
INSTR([url], "http://", -1, 1)
Returns the unescaped version of a Java unicode character escape sequence as a
TEXT
 value.
CASE WHEN [currency] == JAVA_STRING("\u00a5") THEN "yes" ELSE "no" END
Returns a
TEXT
 by concatenating (combining together) the results of multiple
TEXT
 values with the separator in between each non-null value.
JOIN_STRINGS("/", [month], [day], [year])
Extracts a
NUMERIC
 value from a field in a JSON object.
JSON_DECIMAL([top_scores], "test_scores.2")
Extracts a
DOUBLE
 value from a field in a JSON object.
JSON_DOUBLE([top_scores], "test_scores.2")
Extracts an
INTEGER
 value from a field in a JSON object.
JSON_INTEGER([top_scores], "test_scores.2")
Extracts a
LONG
 value from a field in a JSON object.
JSON_LONG([top_scores], "test_scores.2")
Extracts a
TEXT
 value from a field in a JSON object.
JSON_STRING([misc], "hobbies.0")
Returns the count of characters in a
TEXT
 value.
LENGTH([name])
Returns multiple output values packed into a single string of key/value pairs separated by the default key and pair separators. The string returned is in a format that can be read by the
EXTRACT_VALUE
 function.
PACK_VALUES
 uses the same key and pair separator values that
EXTRACT_VALUE
 uses (the Unicode escape sequences
u0003
 and
u0002
, respectively).
PACK_VALUES("ID", [custid], "Age", [age])
Performs a whole string match against a
TEXT
 value with a regular expression and returns the portion of the string matching the first capturing group of the regular expression.
REGEX([email], "^[a-zA-Z0-9._%+-]+@
([a-zA-Z0-9._-]+)
\.[a-zA-Z]{2,4}$")
Evaluates a
TEXT
 value against a regular expression to determine if there is a match, and replaces matched strings with the specified replacement value.
REGEX_REPLACE([phone_number], "([0-9]{3})\.([[0-9]]{3})\.([[0-9]]{4})", "\($1\) $2-$3")
Returns the characters of a string value in the opposite order.
REVERSE("123 Main Street")
Returns the specified characters of a
TEXT
 value based on the given start and optional end position.
SUBSTRING([name], 0, 1)
Converts all alphabetic characters in a
TEXT
 value to lower case.
TO_LOWER("123 Main Street") returns
123 main street
Returns a
TEXT
 value with the first letter of each word capitalized.
TO_PROPER("123 Alameda de las Pulgas, San Mateo CA")
Converts all alphabetic characters in a
TEXT
 value to upper case.
TO_UPPER("123 Main Street")
Removes leading and trailing spaces from a
TEXT
 value.
TRIM([area_code])
Takes XML and returns the first string matching the given XPath expression.
XPATH_STRING([address], "//address[@type='home']/zipcode")

URL Functions

Function
Description
Example
Returns the authority portion of a URL string. The authority portion of a URL is the part that has the information on how to locate and connect to the server.
URL_AUTHORITY("http://user:password@mycompany.com:8012/mypage.html")
Returns the fragment portion of a URL string.
URL_FRAGMENT("http://workday.com/news.php?topic=press#Workday%20News")
Returns the host, domain, or IP address portion of a URL string.
URL_HOST("http://user:password@mycompany.com:8012/mypage.html")
Returns the path portion of a URL string.
URL_PATH("http://workday.com/company/contact.html")
Returns the port portion of a URL string.
URL_PORT("http://user:password@mycompany.com:8012/mypage.html")
Returns the protocol (or URI scheme name) portion of a URL string.
URL_PROTOCOL("http://www.workday.com")
Returns the query portion of a URL string.
URL_QUERY("http://workday.com/news.php?topic=press&timeframe=today")
Decodes a
TEXT
 value that has been encoded with the
application/x-www-form-urlencoded
 media type.
URLDECODE("N%2FA%20or%20%22not%20applicable%22")

Window Functions

Function
Description
Example
AVG
Is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the average of all valid numeric values in the group. It sums all values in the group and divides by the number of valid (NOT NULL) rows. You can use
AVG
 to calculate moving averages.
AVG([Sales]) OVER(
 PARTITION BY [Employee]
 ORDER BY [SalesDate] DESC
 ROWS UNBOUNDED PRECEDING)
Is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the total number of valid rows (NOT NULL) in the group. You can use
COUNT
 together with other functions to calculate cumulative aggregates.
COUNT([Sales]) OVER(
 PARTITION BY [Employee]
 ORDER BY [SalesDate] DESC
 ROWS UNBOUNDED PRECEDING)
Is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the value from the first row in the group.
LAG
Is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the value of a field in the row at the specified offset before (above) the current row in the group.
[Salary] - (LAG([Salary], 1, [Salary]) OVER(
   PARTITION BY [Employee_Name] 
   ORDER BY [Eff_Date] ASC)
   )
Is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the value from the last row in the group.
Is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the value of a field in the row at the specified offset after (below) the current row in the group.
[Salary] - (LEAD([Salary], 1, [Salary]) OVER(
     PARTITION BY [Employee_Name] 
     ORDER BY [Eff_Date] DESC)
     )
MAX
Is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the maximum (highest) value in the group.
MAX([Comp Change]) OVER(
  PARTITION BY [Supervisory Org], [Quarter] 
  ORDER BY [Comp Change] DESC 
  ROWS BETWEEN UNBOUNDED PRECEDING
  AND UNBOUNDED FOLLOWING
  )
MIN
Is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the minimum (lowest) value in the group.
MIN([Comp Change]) OVER(
  PARTITION BY [Supervisory Org], [Quarter] 
  ORDER BY [Comp Change] ASC
  ROWS BETWEEN UNBOUNDED PRECEDING
  AND UNBOUNDED FOLLOWING
  )
Is a window aggregate function used to assign a ranking number to each row in a group. If multiple rows have the same ranking value (there's a tie), then Workday assigns the same rank value to the tied rows and skips the subsequent rank position.
RANK() OVER(
  PARTITION BY [Employee]
  ORDER BY [Sales] DESC)
Is a window aggregate function that partitions rows into groups, orders rows by a field, and assigns a unique, sequential number to each row in a group, starting at 1 for the first row in each group.
ROW_NUMBER
 always assigns a unique value to each row in a group. You might want to use
ROW_NUMBER
 to create a unique ID for each row in your dataset.
ROW_NUMBER() OVER(
 PARTITION BY [Employee]
 ORDER BY [Sales] DESC)
SUM
Is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the total of all values in the group. You can use
SUM
 to calculate running totals.
SUM([fieldA]) OVER(
 PARTITION BY [fieldB]
 ORDER BY [Month]
 RANGE 11 PRECEDING)

Arithmetic Operators

Operator
Meaning
Example
+
Addition
[amount] + 10
Add 10 to the value of the [amount] field.
-
Subtraction
[amount] - 10
Subtract 10 from the value of the [amount] field.
*
Multiplication
[amount] * 100
Multiply the value of the [amount] field by 100.
/
Division
[bytes] / 1024
Divide the value of the [bytes] field by 1024 and return the quotient.

Comparison Operators

Operator
Meaning
Example
=
 or
==
Equal to
[order_date] = "12/22/2016"
>
Greater than
[age] > 18
!>
Not greater than (equivalent to
<
)
[age] !> 8
<
Less than
[age] < 30
!<
Not less than (equivalent to
>=
)
[age] !< 12
>=
Greater than or equal to
[age] >= 20
<=
Less than or equal to
[age] <= 29
<>
 or
!=
 or
^=
Not equal to
[age] <> 30
BETWEEN
min_value
 AND
max_value
Test whether a date or numeric value is within the
min
 and
max
 values (inclusive).
[year] BETWEEN 2014 AND 2016
IN(
list
)
Test whether a value is within a set.
[product_type] IN("tablet","phone","laptop")
LIKE("
pattern
")
Simple inclusive case-insensitive character pattern matching. The
*
 character matches any number of characters. The
?
 character matches exactly 1 (a single) character.
[last_name] LIKE("?utch*")
Matches
Kutcher
,
hutch
 but not
Krutcher
 or
crutch
[company_name] LIKE("workday")
Matches
Workday
 or
workday
value
IS NULL
Check whether a field value or expression is null (empty).
[ship_date] IS NULL
Evaluates to
true
 when the
ship_date
 field is empty

Logical Operators

Operator
Meaning
Example
AND
Test whether 2 conditions are true.
OR
Test if either of 2 conditions are true.
NOT
Reverses the value of other operators.
  • year
    NOT
     BETWEEN 2013 AND 2016
  • first_name
    NOT
     LIKE("Jo?n*")
    Excludes
    John
    ,
    jonny
     but not
    Jon
     or
    Joann
  • Weekday
    NOT
     IN("Saturday", "Sunday")
  • purchase_date IS
    NOT
     NULL
    Evaluates to
    true
     when the
    purchase_date
     field is not empty

This content is not part of the Workday Administrator Guide.