Skip to main content

What’s New

Qrvey 9.0
Qrvey Version 9.0 is now available! This release introduces multi-platform hosting (Azure & AWS), a redesigned Dashboard, extensive widget customizations, and numerous features, enhancements, bug fixes, and performance improvements.
Learn More
Qrvey 8.8
Qrvey Version 8.8 (LTS) is now available to customers! This version supports FIPS for GovCloud and includes tons of bug fixes and performance improvements.
Learn More
Qrvey 8.7
Version 8.7 of the Qrvey platform is now available to customers! This version includes new features including area charts, the ability to pivot and export data, as well as numerous bug fixes and performance improvements.
Learn More
Qrvey 8.6
Version 8.6 of the Qrvey platform is now available to customers. This version includes several new feature enhancements and performance improvements.
Learn More
Required Update for 8.5.1
Attention 8.5.1 customers: for any 8.5.1 instance deployed prior to 08/05/2024, an update is required to ensure you are running the latest images.
Learn More
Qrvey 8.5
Version 8.5 (LTS) of the Qrvey platform is now available to customers. This version includes several new features and performance improvements.
Learn More
End-of-life Schedule
We've added a new article that lists the features and endpoints that have been scheduled for deprecation. All features and endpoints will be supported for (1) year after the release date of the LTS version that contains the alternative.
Learn More
Version: 9.0

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-composer-formula-builder-82 icon on the toolbar.
  2. The Formulas dialog will display a list of existing formulas.

Create a Formula

formula-builder-data-engine-syntax-82

  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.

chart-builder-with-formulas-82

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.

formula-builder-standardized-syntax-82

  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. Error detection in Standardized Syntax Mode
  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.

formula-builder-example-standardized-syntax

Test a Formula with Standardized Syntax

Formula Builder Testing tab

  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.
SymbolOperation
*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 NameTypeDescription
ABSNumericReturns the absolute value of a number.
ANDLogicalEvaluates multiple logical tests and returns true if all are true, otherwise returns false.
CONCATENATEString/TextJoins two or more strings into one string.
DATEADDDate/TimeAdds 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.
DATESUBTRACTDate/TimeSubtracts 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.
DATEDIFDate/TimeCalculates the difference between two dates based on the date part requested. Date part allows: "Y" years, "M" months, "D" days.
DAYDate/TimeReturns the day of a date. The day is given as an integer ranging from 1 to 31.
DAYOFWEEKDate/TimeReturns a number or string representative of the weekday for a given date.
EVENNumericReturns a number rounded to the next greatest even integer.
EXPNumericReturns Euler’s number e raised to the power of a number.
HOURDate/TimeReturns the hour of a datetime value as an integer ranging from 0 to 23.
IFLogicalPerforms a logical test and returns a value based on the result. Requires values for both true and false outcomes.
IFSLogicalAllows multiple logical tests and returns the value for the first condition that evaluates to true.
INCLUDEString/TextReturns true if a string contains a specified sequence of characters. Case sensitive.
ISNULLLogicalReplaces null values with the specified replacement value.
LEFTString/TextReturns the first characters in a text string, based on a specified number of characters.
LENGTHString/TextReturns the length of a string.
LOGNumericReturns the logarithm of a number to the base you specify.
LOWERString/TextConverts all characters in a string to lowercase.
MAXNumericReturns the greater of two or more numeric or datetime values.
MIDString/TextReturns a specific number of characters from a text string, starting at a specified index.
MILLISECONDDate/TimeReturns the millisecond of a datetime value as an integer ranging from 0 to 999.
MINNumericReturns the smaller of two or more numeric or datetime values.
MINUTEDate/TimeReturns the minute of a datetime value. The minute is an integer from 0 to 59.
MONTHDate/TimeReturns the month of a date as an integer ranging from 1 (January) to 12 (December).
ODDNumericReturns a number rounded to the next greatest odd integer.
ORLogicalEvaluates logical tests and returns true if at least one test is true, false if all are false.
POWERNumericReturns the value of the first argument raised to the power of the second argument.
PROPERString/TextCapitalizes the first letter of each word, converting the rest to lowercase.
RANDOMNumericReturns a positive decimal number between 0 (inclusive) and 1 (exclusive).
REPLACEString/TextReplaces a specified sequence of characters in a string with a different sequence. Case sensitive.
RIGHTString/TextReturns the last characters in a text string, based on a specified number of characters.
ROUNDNumericRounds a number to a specified number of digits.
ROUNDDOWNNumericRounds a number down to a specified number of digits.
ROUNDUPNumericRounds a number up to a specified number of digits.
SECONDDate/TimeReturns the second of a datetime value as an integer from 0 to 59.
SQRTNumericReturns the positive square root of a value.
TEXTString/TextConverts a number into a string.
TRIMString/TextRemoves all leading and trailing spaces from text.
UPPERString/TextConverts all characters in a string to uppercase.
YEARDate/TimeReturns the year of a date as an integer ranging from 1900 to 9999.

Data Engine Syntax (Legacy)

formula-builder-data-engine-syntax-82

  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.

FunctiondateFormat
SyntaxdateFormat(date_value, 'format')
ExampledateFormat([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.

Functionagg_sum
Syntaxagg_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+
Syntaxvalue1 + 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).

Functionif
Syntaxif (condition) {return result1;} else {return result2;}
Exampleif(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.

    SymbolOperation
    .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.
    ANDLogical and of two values.
    ORLogical or of two values.
    NOTLogical 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:

LetterDate or Time ComponentPresentationExamples
GEra designatorTextAD
y,YYearYear1996; 96
MMonth in yearMonthJuly; Jul; 07
wWeek in yearNumber27
WWeek in monthNumber2
DDay in yearNumber189
dDay in monthNumber10
FDay of week in monthNumber2
EDay name in weekTextTuesday; Tue
eDay number of week (1 = Monday, ..., 7 = Sunday)Number1
aAM/PM markerTextPM
HHour in day (0-23)Number0
kHour in day (1-24)Number24
KHour in AM/PM (0-11)Number0
hHour in AM/PM (1-12)Number12
mMinute in hourNumber30
sSecond in minuteNumber55
SMillisecondNumber978
zTime zoneGeneral time zonePST; GMT-08:00
ZTime zoneRFC 822 time zone-0800
xTime zoneISO 8601 time zone-08; -0800; -08:00