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.

1. Click the icon on the toolbar.
2. The Formulas dialog will display a list of existing formulas.

Create a Formula

1. To create a new formula, click Create Formula. The Create Formula dialog will appear.
2. 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.

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.

1. Open the Formula Builder interface and select Standardized Syntax.
2. Enter a name in the Formula Name box.
3. 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.
4. 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.
5. 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.
6. 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.”
7. 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

1. 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.
2. 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.

3. 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.
4. 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 or false 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)

1. Open the Formula Builder interface and select Data Engine Syntax (Legacy).
2. Enter a name in the Formula Name box.
3. 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.
   note

   The 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.
4. 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.
5. Continue adding functions and columns until your formula is complete.
6. 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.
7. 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

note

• 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