Formula fields
Formula fields (also known as K-fields) are record-level fields that store a value calculated on the basis of other fields, using a spreadsheet-formula syntax. For example, you may have three different surveys with a different Likelihood to Recommend (LTR) question for each survey, and want to see these three questions rolled together into a single LTR metric in reports and exports. This can be accomplished by creating a Formula field that returns the appropriate LTR Feedback field for each survey, which can then be added to reports and exports.
Note that any change in a field referenced by the calculation automatically updates the Formula field value. For example, if LTR 1 question originally has a value of 8 and then it changes 7, the Formula field that returns the appropriate LTR Feedback field for that question also changes.
For information on how to configure all field properties, see Program field editor.
Formula definition
On the Field editor, navigate to Formula Definition to build a Formula field using a:
-
Function — This resembles the syntax used in commonly used spreadsheet tools, such as Microsoft Excel or Google Spreadsheets. See Functions below for more details.
-
Field — Optional. Build a formula using any available Case form, Contact, Event, Feedback, and System fields. Existing K-fields created in Setup cannot be used.
-
Operator — Operators specify the type of calculation to perform on the fields in a formula. See Operators below for more details.
Test your formula definition by selecting either Use a random survey record or Enter a specific survey record (followed by the survey Record ID) from the Test Formula dropdown, then clicking Test. A message appears letting you know if the test succeeded or failed.
Calculation modes
Formula field values are automatically calculated using different calculation modes depending on the function and field(s) that are part of the formula. However, you may choose a particular mode in certain cases to fit your needs. For example, consider a formula that weights some score fields and returns a fractional value to use in a dial or score over time report modules. In that case, the default calculation mode is On-demand, which prevents you from using it for those modules, but you can manually modify the calculation mode to be During field refresh if needed.
-
Instantly — Previously known as Available. Values are calculated immediately upon saving at run-time for all existing records where they are used, and for new records as they enter Medallia Experience Cloud. For example, if a Formula field is part of a report filter, the value that results from the field calculation happens at the time the report is loaded on the instance. However, the Formula field value for existing records is only recalculated when the survey is updated, during a field refresh calculation, the formula calculation is updated in a material way (modifying blank lines, adding, or removing comments does not count), or if there is a deployment that causes a cache rebuild (which is similar to a field refresh calculation mode). This means that if your Formula field depends on other data sources, such as a lookup table, it does not recalculate because the lookup table is updated.
Additionally, Instant calculation is not applied to multi-valued Formula fields.
-
On-demand — Previously known as Export only. Field values are calculated on-demand as either one of the fields or functions in the formula required so. Common uses include conditions, such as in Alerts, Case Forms, record-level display purposes (responses filters and forms), Email templates, or if the Formula field is multi-valued. This mode is often used when you don't need filtering or aggregation in reports. To change the formula calculation mode to During field refresh, select Calculate during field refresh.
-
During field refresh — Previously known as Backfill required. Values are calculated for the entire dataset against the database as new records enter Experience Cloud. Existing records do not have a value for this field until either the individual record is updated (any Feedback or Event field value change) or in a field refresh calculation. Changes to the calculation are not automatically for existing records, so you must manually perform a field refresh calculation to update existing records. To calculate the formula using the On-demand mode, select Calculate on-demand. Additionally, this calculation mode is applied to multi-valued Formula fields.
Functions
These tables list the different functions available to build Formula fields, along with descriptions on how to use them, required data, and common examples.
Date and time functions
| Function | Description | Parameters | Output data type | Syntax | Example |
|---|---|---|---|---|---|
| DATE | Returns a date value from year, month, and day values you enter. | Year (YYYY), Month (MM), Day (DD) | Date | DATE(year, month, day) | DATE("2020", "01", "01") |
| DATEDIF | Calculates the number of days, months, or years between two dates. | Date, Date, Type | Integer | DATEDIF(date, date, type) | DATEDIF([Creation date] , [Response date], "D") |
| DATETIMEVALUE | Returns a year, month, day and GMT time value. | Text | Date/Time | DATETIMEVALUE(datetime) | DATETIMEVALUE("2021-02-01T09:48:41.782Z") |
| DATEVALUE | Returns a date value for a date/time or text expression. | Text | Date | DATEVALUE(date) | DATEVALUE("2020-01-01") |
| DAY | Returns a day of the month in the form of a number between 1 and 31. | Date | Integer | DAY(date) | DAY(DATE("2020", "01", "15")) |
| HOUR | Returns the local time hour value without the date in the form of a number from 1 through 12. | Date/Time | Integer | HOUR(datetime) | HOUR(NOW()) |
| MINUTE | Returns a minute value in the form of a number from 0 through 60. | Date/Time | Integer | MINUTE(datetime) | MINUTE(NOW()) |
| MONTH | Returns the month, a number between 1 (January) and 12 (December) in number format of a given date. | Date | Integer | MONTH(date) | MONTH(NOW()) |
| NOW | Returns a date/time representing the current moment. | Datetime | NOW() | NOW() | |
| SECOND | Returns a seconds value in the form of a number from 0 through 60. | Datetime | Integer | SECOND(datetime) | SECOND([Response date]) |
| TODAY | Returns the current date as a date data type. | Date | TODAY() | TODAY() | |
| WEEKDAY | Returns the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday. | Date | Integer | WEEKDAY(date) | WEEKDAY([Response date]) |
| YEAR | Returns the four-digit year in number format of a given date. | Date | Integer | YEAR(date) | WEEKDAY([Response date]) |
| day_of_week(date1, date2) | Checks which days of the week are between the two given dates; returns 1 if weekdays only, 2 if weekend only, and 3 if both. | day_of_week(date1, date2) | day_of_week([Alert close time], [Response date]) | ||
| daysDifferenceBy24Hours(date1, date2) | Returns the number of days between the two dates, counting 24 hours as one day (rounded down). | daysDifferenceBy24Hours(date1, date2) | daysDifferenceBy24Hours([Response date], [Creation date]) | ||
| daysDifferenceByMidnights(date1, date2) | Returns the number of days between the two dates, counting each midnight as a new day. | daysDifferenceByMidnights(date1, date2) | daysDifferenceByMidnights([Response date], [Creation date]) | ||
| formatDate(date, format) | Returns the given date formatted according to the given DateFormat pattern. | formatDate(date, format) | formatDate([Response date], "DD-MM-YY") | ||
| formatMsecs(number, TimeUnit) | Returns a number of milliseconds formatted to a more readable time length. | formatMsecs(number, TimeUnit) | formatMsecs(60000) | ||
| hoursDifference(date1, date2) | Returns the number of hours between the two dates. | hoursDifference(date1, date2) | hoursDifference([Response date], [Creation date]) | ||
| millisDifference(date1, date2) | Returns the number of milliseconds between the two dates. | millisDifference(date1, date2) | millisDifference([Creation date], [Accepted for reporting on date]) | ||
| withTimezone(date, timeZone ID) | Returns a date, transposing it to the indicated timezone. Timezones are indicated by ID. | withTimezone(date, timeZone ID) | withTimezone([Response date], "America/Los_Angeles") |
Number and string (Math) functions
| Function | Description | Parameters | Output data type | Syntax | Example |
|---|---|---|---|---|---|
| ABS | Calculates the absolute value of a number. The absolute value of a number is the number without its positive or negative sign. | Fractional | Fractional | ABS(value) | ABS(-1) |
| CEILING | Rounds a number up to the nearest integer, away from zero if negative. | Fractional | Integer | CEILING(value) | CEILING(1.5) |
| EXP | Returns a value for e raised to the power of a number you specify. | Fractional, Fractional | Fractional | EXP(value, value) | EXP(2, 3) |
| FLOOR | Returns a number rounded down to the nearest integer, towards zero if negative. | Fractional | Integer | FLOOR(value) | FLOOR(1.5) |
| MAX | Returns the highest number from a list of numbers. | Fractional (multi) | Fractional | MAX(value, value, ...) | MAX(2, 3) |
| MIN | Returns the lowest number from a list of numbers. | Fractional (multi) | Fractional | MIN(value, value, ...) | MIN(2, 3) |
| MOD | Returns a remainder after a number is divided by a specified divisor. | Fractional, Fractional | Integer | MOD(value, value) | MOD(11, 3) |
| PRODUCT | Multiplies all given numbers and returns the product. | Fractional (multi) | Fractional | PRODUCT(value, value) | PRODUCT(2,3) |
| QUOTIENT | Returns the integer portion of a division. | Fractional | Fractional | QUOTIENT(value, value) | QUOTIENT(2,3) |
| ROUND | Returns the nearest number to a number you specify, constraining the new number by a specified number of digits. | Fractional | Integer | ROUND(value) | ROUND(1.49) |
| RAND | Returns a random number between 0 and 1. | Fractional | RAND() | RAND() | |
| SUM | Returns the sum of the given integers. | Fractional (multi) | Fractional | SUM(value, value, ...) | SUM(1.49, 1.51) |
| between | Returns true if the first parameter is in the inclusive range formed between the second and third parameters. | between(number1, number2, number3) | between(3, 2, 4) |
Text functions
| Function | Description | Parameters | Output data type | Syntax | Example |
|---|---|---|---|---|---|
| EXACT | Checks to see if two text values are identical. | Text, Text | Boolean | EXACT(text, text) | EXACT([Name], [First name]) |
| LEFT | Returns the leftmost characters from a text value. | Text, Integer | Text | LEFT(text, num_chars) | LEFT("bbba", 3) |
| LEN | Returns the number of characters in a text string. | Text | Integer | LEN(text) | LEN([LTR Comment]) |
| LOWER | Converts text to lowercase. | Text | Text | LOWER(text) | LOWER("HELLO") |
| MID | Returns a specific number of characters from a text string starting at the position you specify. | Text, Integer, Integer | Text | MID(text, start_int, end_int) | MID("bbba", 1, 3) |
| REPLACE | Replaces characters within text. | Text, Integer, Integer, Text | Text | REPLACE(text_to_search, start_int, end_int, text_to_insert) | REPLACE("abbba", 1, 4, "ccc") |
| RIGHT | Returns the rightmost characters from a text value. | Text, Integer | Text | RIGHT(text, num_chars) | RIGHT("bbba", 3) |
| SEARCH | Finds one text value within another (not case-sensitive). | Text, Text | Integer | SEARCH(text_to_search, search_for) | SEARCH("bbba", "a") |
| SUBSTITUTE | Finds and replaces matching text with new provided text. | Text, Text, Text | Text | SUBSTITUTE(text_to_search, search_for, replace_with)Screen reader support enabled. | SUBSTITUTE("abbba", "b", "c") |
| TEXT | Formats a number and converts it to text. | Fractional (or anything) | Text | TEXT(value) | TEXT([Likelihood to Recommend]) |
| TEXTJOIN | Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges. | Delimiter, Boolean, Text (multi) | Text | TEXTJOIN(delimeter, ignore_empty, text1, text2, .....) | TEXTJOIN(", ", false, [Last name], [First name]) |
| TRIM | Removes spaces from text. | Text | Text | TRIM(text) | TRIM(" hello ") |
| UPPER | Converts text to uppercase. | Text | Text | UPPER(text) | UPPER([First name]) |
| VALUE | Converts a text argument to a number. | Text | Fractional | VALUE(text) | VALUE("1234") |
Logic functions
| Function | Description | Parameters | Data output type | Syntax | Example |
|---|---|---|---|---|---|
| AND | Returns true if all of its arguments are true. | Boolean (multi) | Boolean | AND(boolean, boolean, ...) | AND(2>1, ISNULL([Likelihood to Recommend]),ISNUM(1)) |
| IF | Returns a value depending on whether the condition provided with the function evaluates as true or false. | Boolean, VAL, VAL | VAL | IF(boolean, value, value) | IF(ISNULL([Additional Comments]), "Yes", "No") |
| ISNULL | Tests if the value of an expression is null. If so, it returns true; otherwise, it returns false. | VAL | Boolean | ISNULL(value) | ISNULL([Additional Comments]) |
| ISNUM | Returns true if the expression can be interpreted as a number; otherwise, it returns false. | VAL | Boolean | ISNUM(value) | ISNUM(1) |
| ISTEXT | Returns true if the expression can be interpreted as a text, otherwise, it returns false. | VAL | Boolean | ISTEXT(value) | ISTEXT("1") |
| NOT | Reverses the logic of its argument. | Boolean | Boolean | NOT(boolean) | NOT(OR(2>1, ISNULL([Likelihood to Recommend]),ISNUM(1))) |
| NULL | Returns a null value. | Null | NULL() | NULL() | |
| OR | Returns true if any of its arguments are true. | Boolean (multi) | Boolean | OR(boolean, boolean, ...) | OR(2>1, ISNULL([Likelihood to Recommend]),ISNUM(1)) |
| coalesce | Returns first of the objects that is not null. | coalesce(object1, object2 ...) | coalesce([First name], [Last name], [Name]) | ||
| list | Creates a list (array) from a set of values. It includes null values and automatically flattens values that are already arrays. | list(value1, value2 ...) | list(1, 2, 3) |
Org hierarchy functions
| Function | Description | Syntax | Example |
|---|---|---|---|
| getUnitDatafield | Returns the value of the Unit Data field for the given unit. | getUnitDatafield(unit, text) | getUnitDatafield([Store], "overdue_alert_email") |
| getUnitGroupDatafield | Retrieves a Unit group Data field value by descending through the levels of Unit group children from the starting node. Descending will stop once there are no more children or the maxDescendantLevel has been reached. If the given Unit belongs to more than one leaf unit group at the final level, an exception will be thrown because we cannot be sure which is the desired value. If there is more than one leaf, then the setup will need to change. Returns a string value of the one Unit Group Data field found or null if not. | getUnitGroupDatafield(unit, text, integer, text) | getUnitGroupDatafield([Account], 'b2b_region', 1, 'name') |
| getUnitGroupFromOrgHierarchy | Same as getUnitGroupsFromOrgHierarchy but returns only the first element or null if there are no matches. Note: Use this function in cases where there can only be a single match. | getUnitGroupFromOrgHierarchy(text) | getUnitGroupFromOrgHierarchy('store_region') |
| getUnitGroupFromOrgHierarchyOnField | Similar to getUnitGroupFromOrgHierarchy but allows for limiting the search to a single unit field. Note: Use this function in cases where there can only be a single match | getUnitGroupFromOrgHierarchyOnField(text, text) | getUnitGroupFromOrgHierarchyOnField('employee_location_country', [Employee]) |
| getUnitGroupsFromOrgHierarchy | Returns the Unit groups under the specified Unit group hierarchy, using the Org Hierarchy information stored on each record (a_org_hierarchy). This function is intended to be used together with the Org hierarchy filtering feature. | getUnitGroupsFromOrgHierarchy(text) | getUnitGroupsFromOrgHierarchy('store_region') |
| getUnitGroupsFromOrgHierarchyOnField | Similar to getUnitGroupsFromOrgHierarchy, but allows for limiting the search to a single unit field. | getUnitGroupsFromOrgHierarchyOnField(text, text) | getUnitGroupsFromOrgHierarchyOnField('employee_location_country', [Employee]) |
| isMemberOfUnitGroup | Returns true if the given unit is member of the Unit group with the given untranslated name. | isMemberOfUnitGroup(unit, unitgroup) | isMemberOfUnitGroup(unit("cc_agent_117"), "USA") |
| isUnitMemberOfUnitGroup | Returns true if the Unit is a member of the Unit group with the given identifier. | isUnitMemberOfUnitGroup(unit, unitgroup | isUnitMemberOfUnitGroup(unit("cc_agent_117"), "cc_teamlead_20") |
| unit | Returns the Unit with the given identifier. | unit(text) | unit("cc_agent_117") |
Medallia functions
| Function | Description | Parameters | Output data type | Syntax | Example |
|---|---|---|---|---|---|
| alternativeName | Returns the name associated to a choice set. | name(Field name) | name([Likelihood to Recommend]) | ||
| companyName | Returns the name of the company. | companyName() | companyName() | ||
| embeddedApp | Return the URI to an Embedded App; the first parameter is the name of the embedded app and the rest serves as request parameters key/value pairs. | embeddedApp | embeddedApp("embedded_app_label") | ||
| environmentURL | Returns the URL of the application. Calculation is only done for On-demand mode, and it should not be used by a field that is not On-demand mode. | environmentURL() | environmentURL() | ||
| intFromPossibleEnum | Returns the ordinal of choice set or number. | intFromPossibleEnum(Field name) | intFromPossibleEnum(seqnum([Likelihood to Recommend])) | ||
| numeric | Returns the numerical value of the given choice set; otherwise, it returns null. | numeric(Field name) | numeric([Likelihood to Recommend]) | ||
| seqnum | Returns the sequence number of the given choice set. | seqnum(Field name) | seqnum([Likelihood to Recommend]) | ||
| text | Returns the static text representation of the value of a field. | text(Field name) | text([Likelihood to Recommend]) |
Operators
Operators specify the type of calculation to perform on the fields in a formula. Select from the following operators to perform either basic mathematical operations to produce numeric results, or to compare two values to result in true or false.
| Name | Symbol | Description | Example |
|---|---|---|---|
| ADD | + | Addition. Calculates the sum of two values or concatenates strings. | ABS(10+2) |
| SUBTRACT | - | Subtraction. Calculates the difference of two values. | ABS(10-2) |
| MULTIPLY | * | Multiplies two values. | ABS(10*2) |
| DIVIDE | / | Divides two values | ABS(10/2) |
| PARENTHESIS | () | Enforces the order of operations | ABS(10+(2*6)) |
| EQUALS | = | The field matches the value | ABS(Duration = 2) |
| GREATER THAN | > | The field is greater than the value you choose. | ABS(Duration > 2) |
| LESS THAN | < | The field is less than the value you choose. | ABS(Duration < 2) |
| GREATER THAN OR EQUAL TO | >= | The field is greater than or equal to the value you choose. | ABS(Duration >= 2) |
| LESS THAN OR EQUAL TO | <= | The field is less than or equal to the value you choose. | ABS(Duration <= 2) |
