Formulas
The Formula Builder is an interactive user interface that enables you to quickly create, test, and save formulas. Formulas allow you to create new columns of data that do not already exist in the dataset. For example, you can create a formula to segment data, aggregate data, calculate ratios, and much more. Once a formula is created and saved, it becomes available in Chart Builder, Pixel-perfect Reports, and the Analyze widget as a new column that can be added to a chart or visualization. The Qrvey platform computes the values of that column in real time when the end user displays the chart or visualization.
View Formulas
The Formula Builder can be accessed from within Chart Builder, Pixel-perfect Reports, or the Analyze widget.
- Click the
icon on the toolbar.
- The Formulas dialog will display a list of existing formulas.
Create a Formula
- To create a new formula, click Create Formula. The Create Formula dialog will appear.
- Configure the formula as desired:
- Formula Mode:
- Data Engine Syntax (legacy) — Create formulas using a database-specific syntax.
- Standardized Syntax — Create formulas using a standardized syntax compatible with any database connected to the Qrvey platform.
- Formula — Write the formula in this section.
- Standardized Syntax provides a Testing tab, enabling you to define input data to test your formula.
- Data Engine Syntax provides a Test link to test the formula using random values from the dataset.
- Insert Functions — Select functions to add to your formula.
- Insert Columns — Choose dataset columns to include in your formula.
- Formula Mode:
After creating and saving a formula, it will appear in the Data panel alongside other dataset columns, indicated by the "Sigma" icon before the name. You can use formulas in the same way as other columns across various features, including charts, filters, Automation, and Pixel-perfect Reports.
Standardized Syntax
Standardized Syntax Mode allows you to create formulas using a standardized language rather than a database-specific syntax. Formulas created in Standardized Syntax Mode work with any database engine supported by the Managed Connect and Live Connect features.
- Open the Formula Builder interface and select Standardized Syntax.
- Enter a name in the Formula Name box.
- To add a function to the formula, type it directly in the editor. A suggestion box will display columns and functions matching your input. Alternatively:
- Under Insert Functions, select the desired function. A description box will provide helpful information about the function.
- Click Add to Formula. The function will appear in the Formula box.
- To add a column to the formula, type it directly in the editor. A suggestion box will display matching columns and functions. Alternatively:
- Place the cursor in the desired location in the formula.
- Under Insert Columns, click the column you want to insert. The column will appear in the Formula box.
- Continue adding functions and columns until your formula is complete. Standardized Syntax Mode provides automatic error detection. The editor continuously evaluates formulas as you type and immediately notifies you of syntax errors or incorrect data type usage. Hover over a highlighted error to view additional details.
- Before using the formula, test it with sample data. If your formula uses correct syntax, the Testing tab will be available. For more information, see the next section, “Testing a Formula in Standardized Syntax.”
- Click Save to save the formula. In Standardized Syntax Mode, the Save button is displayed on the Formula tab.
Test a Formula with Standardized Syntax
- In Standardized Syntax mode, create your formula and click Testing. If the formula contains syntax errors or incorrect data type usage, the Testing tab will not be available.
- The Input box lists all the columns in the formula in JSON format and assigns values from the dataset to those columns.
- In the Input box, review the values provided and modify them as needed. To obtain a new set of values from the dataset, click Randomize Data.
Note: You can evaluate more than one set of values at a time by editing the JSON and adding another set of columns.
- Click Test Formula. The output will display in the Output box. If an error occurs during formula evaluation, click See Console Output to view a detailed error response from the database engine.
- Click Save. The formula is now ready for use.
Syntax Reference
This section provides a reference for proper syntax when using Standardized Syntax Mode.
- Field/Column Syntax — Insert columns using the Insert Columns dropdown, by entering the column name, or by selecting them from the Column/Function suggestion box. Columns will display as pills inside the formula.
- Function Syntax — Function names must be in uppercase and followed by a bracketed argument list. For example:
ABS(25)
- Comments Syntax — Add single-line comments to the formula using
//
. For example:// This is a comment
. - Operator Syntax — The following table lists the available operators. Note that normal operator precedence applies.
Symbol | Operation |
---|---|
* | Multiplication of two numbers. |
/ | Division of two numbers. |
+ | Addition of two numbers. |
- | Subtraction of two numbers, or negation of a number. |
= | Test equality of two values. |
> | Test if the first number is greater than the second number. |
< | Test if the first number is less than the second number. |
>= | Test if the first number is greater than or equal to the second number. |
<= | Test if the first number is less than or equal to the second number. |
<> | Test inequality of two values. |
( ) | Evaluate the bracketed expression before applying operators to it. |
Supported Values
Supported data types include:
- Numbers: Includes decimals and negative numbers.
- Strings: Any sequence of characters enclosed in quotes.
- Date/Time Values: Strings in formats such as "MM/DD/YYYY" or "MM/DD/YYYY HH:MM:SS".
- Boolean:
true
orfalse
values.
Supported Functions
The following table lists supported functions in Standardized Syntax Mode:
Function Name | Type | Description |
---|---|---|
ABS | Numeric | Returns the absolute value of a number. |
AND | Logical | Evaluates multiple logical tests and returns true if all are true , otherwise returns false . |
CONCATENATE | String/Text | Joins two or more strings into one string. |
DATEADD | Date/Time | Adds a specific date part value to a given date. Date part allows: "Y" years, "M" months, "D" days, "H" hours, "MI" minutes and "S" seconds. |
DATESUBTRACT | Date/Time | Subtracts a specific date part value from a given date. Date part allows: "Y" years, "M" months, "D" days, "H" hours, "MI" minutes and "S" seconds. |
DATEDIF | Date/Time | Calculates the difference between two dates based on the date part requested. Date part allows: "Y" years, "M" months, "D" days. |
DAY | Date/Time | Returns the day of a date. The day is given as an integer ranging from 1 to 31. |
DAYOFWEEK | Date/Time | Returns a number or string representative of the weekday for a given date. |
EVEN | Numeric | Returns a number rounded to the next greatest even integer. |
EXP | Numeric | Returns Euler’s number e raised to the power of a number. |
HOUR | Date/Time | Returns the hour of a datetime value as an integer ranging from 0 to 23. |
IF | Logical | Performs a logical test and returns a value based on the result. Requires values for both true and false outcomes. |
IFS | Logical | Allows multiple logical tests and returns the value for the first condition that evaluates to true. |
INCLUDE | String/Text | Returns true if a string contains a specified sequence of characters. Case sensitive. |
ISNULL | Logical | Replaces null values with the specified replacement value. |
LEFT | String/Text | Returns the first characters in a text string, based on a specified number of characters. |
LENGTH | String/Text | Returns the length of a string. |
LOG | Numeric | Returns the logarithm of a number to the base you specify. |
LOWER | String/Text | Converts all characters in a string to lowercase. |
MAX | Numeric | Returns the greater of two or more numeric or datetime values. |
MID | String/Text | Returns a specific number of characters from a text string, starting at a specified index. |
MILLISECOND | Date/Time | Returns the millisecond of a datetime value as an integer ranging from 0 to 999. |
MIN | Numeric | Returns the smaller of two or more numeric or datetime values. |
MINUTE | Date/Time | Returns the minute of a datetime value. The minute is an integer from 0 to 59. |
MONTH | Date/Time | Returns the month of a date as an integer ranging from 1 (January) to 12 (December). |
ODD | Numeric | Returns a number rounded to the next greatest odd integer. |
OR | Logical | Evaluates logical tests and returns true if at least one test is true, false if all are false. |
POWER | Numeric | Returns the value of the first argument raised to the power of the second argument. |
PROPER | String/Text | Capitalizes the first letter of each word, converting the rest to lowercase. |
RANDOM | Numeric | Returns a positive decimal number between 0 (inclusive) and 1 (exclusive). |
REPLACE | String/Text | Replaces a specified sequence of characters in a string with a different sequence. Case sensitive. |
RIGHT | String/Text | Returns the last characters in a text string, based on a specified number of characters. |
ROUND | Numeric | Rounds a number to a specified number of digits. |
ROUNDDOWN | Numeric | Rounds a number down to a specified number of digits. |
ROUNDUP | Numeric | Rounds a number up to a specified number of digits. |
SECOND | Date/Time | Returns the second of a datetime value as an integer from 0 to 59. |
SQRT | Numeric | Returns the positive square root of a value. |
TEXT | String/Text | Converts a number into a string. |
TRIM | String/Text | Removes all leading and trailing spaces from text. |
UPPER | String/Text | Converts all characters in a string to uppercase. |
YEAR | Date/Time | Returns the year of a date as an integer ranging from 1900 to 9999. |
Data Engine Syntax (Legacy)
- Open the Formula Builder interface and select Data Engine Syntax (Legacy).
- Enter a name in the Formula Name box.
- To add a function to the formula, type it directly in the editor. A suggestion box will display columns and functions matching your input. Alternatively:
- Under Insert Functions, select the desired function. A description box will provide helpful information about the function.
noteThe Insert Functions section is only available for Managed datasets. For Live Connect datasets, use the syntax supported by that dataset.
- Click Add to Formula. The function will appear in the Formula box.
- To add a column to the formula, type it directly in the editor. A suggestion box will display matching columns and functions. Alternatively:
- Place the cursor in the desired location in the formula.
- Under Insert Columns, click the column you want to insert. The column will appear in the Formula box.
- Continue adding functions and columns until your formula is complete.
- Test the formula by clicking Test. The formula will be tested with random values from the dataset. If there is an error, it will be displayed.
- Click Save to save the formula.
Example: Extract Month from Date
You can use the dateFormat
function to extract a month value from a date. For example, if you want to use the month for the x-axis of a bar chart.
Function | dateFormat |
---|---|
Syntax | dateFormat(date_value, 'format') |
Example | dateFormat([orderDate], 'MM') |
Example: Aggregate Values of All Rows
You can create a formula to calculate the aggregate of all the rows in a column, and then use the result in a calculation. For example, to calculate the percentage of one product’s inventory to all product inventory.
Function | agg_sum |
---|---|
Syntax | agg_sum(value) |
Example | [quantityInStock] / agg_sum([quantityInStock]) * 100 |
Example: Concatenate Text Columns
Formula Builder supports concatenation using the +
sign. For example, to create a full name by concatenating the first name and last name columns.
Function | + |
---|---|
Syntax | value1 + value2 + value3... |
Example | [contactLastName] + ',' + [contactFirstName] |
Example: Use an “If” Statement
You can use if
and other scripting functions to create more sophisticated formulas. For example, suppose you want to create a formula that determines whether your shipping department shipped customer orders within three days. To do this, you would create an if
statement that compares two columns in your ERP system, orderDate
and shippedDate
. If the order was shipped within three days, it returns the word “fast.” If not, it returns “slow.” In the example below, notice that the isNull()
statement is used to provide some error-handling by changing a null value into a date value (now
).
Function | if |
---|---|
Syntax | if (condition) {return result1;} else {return result2;} |
Example | if(dateDiff([orderDate], isNull([shippedDate], now()), 'DAYS') <= 3) {return 'fast';} else {return 'slow';} |
Syntax Reference
This section is a reference for proper syntax of formulas in Data Engine Syntax mode.
-
Field/Column Syntax — Use
[ ]
around the field or column name. For example:[Sales]
-
Function Syntax — Function names must be in lowercase and must be followed by a bracketed argument list. For example:
sum([Items])
avg([Temp]) -
Operator Syntax — The following table lists the available operators. Note that normal operator precedence applies.
Symbol Operation .
Apply the function following the dot to the string/text value before the dot. *
Multiplication of two numbers. /
Division of two numbers. +
Addition of two numbers. -
Subtraction of two numbers, or negation of a number. ==
Test equality of two values. >
Test if the first value is greater than the second value. <
Test if the first value is less than the second value. >=
Test if the first value is greater than or equal to the second value. <=
Test if the first value is less than or equal to the second value. !=
Test inequality of two values. ^
Bitwise exclusive or (XOR) of two values. AND
Logical and of two values. OR
Logical or of two values. NOT
Logical not of a value. ( )
Evaluate the bracketed expression before applying operators to it.
Supported Functions
- Numeric Functions — Formula Mode supports the Elasticsearch Math API.
- Text Functions — Formula Mode supports the Elasticsearch Text API.
- Date Functions — Supported date functions include:
now
dateFormat
dateParse
dateAdd
dateSubtract
dateDiff
dateIsNull
Use dateFormat
to Display Dates and Times
The dateFormat
function enables you to determine how to display the date and time. For example:
dateFormat("07/04/2001", "EEE, MMM d, ''yy")
Returns:
Wed, Jul 4, '01
- You may use single or double quotes around the format string.
- All dates are treated as GMT.
The following table lists the formatting arguments:
Letter | Date or Time Component | Presentation | Examples |
---|---|---|---|
G | Era designator | Text | AD |
y,Y | Year | Year | 1996; 96 |
M | Month in year | Month | July; Jul; 07 |
w | Week in year | Number | 27 |
W | Week in month | Number | 2 |
D | Day in year | Number | 189 |
d | Day in month | Number | 10 |
F | Day of week in month | Number | 2 |
E | Day name in week | Text | Tuesday; Tue |
e | Day number of week (1 = Monday, ..., 7 = Sunday) | Number | 1 |
a | AM/PM marker | Text | PM |
H | Hour in day (0-23) | Number | 0 |
k | Hour in day (1-24) | Number | 24 |
K | Hour in AM/PM (0-11) | Number | 0 |
h | Hour in AM/PM (1-12) | Number | 12 |
m | Minute in hour | Number | 30 |
s | Second in minute | Number | 55 |
S | Millisecond | Number | 978 |
z | Time zone | General time zone | PST; GMT-08:00 |
Z | Time zone | RFC 822 time zone | -0800 |
x | Time zone | ISO 8601 time zone | -08; -0800; -08:00 |