Using Formulas in Analytics

In Envisio Analytics, you can use formula expressions to evaluate to a result. An example to use formulas is when you need to calculate the variance between two columns in a data source.

Using Formulas in Analytics

Formulas can be applied in the following instances:

• Formula type columns for Data Sources

• Summary Variables for Summary Label visuals

• Column conditions for Table visuals

• Filters in Organize and Pivot Data Sources

Tip
When referencing columns in Analytics, prefix your column with a dollar sign. E.g. $A means column A

Formula Columns for Data Sources

To set up a data source column using a formula, choose Formula as the Data Type for the column. A long grey box appears, and this is where the formula goes. 

  

In the example above, the formula:

$E-$D means column E subtract column D

Summary Variables for Summary Label Visuals

In the Summary Label visual, you can use the Summary Variable to evaluate an expression based on the column variables defined. 

Tip
In Summary Label visuals, use #[variable_name] to refer to the column variable.

A formula can be used in the Variable Formula field. In the example above, the formula used for the summary variable USDollar:

#[CV1]*0.71 means multiply column variable CV1 by 0.71

Column Conditions for Table Visuals

In Table visuals, you can use Conditional Formatting on columns to highlight columns that meet set conditions.

 

In the example above, column D uses the condition:

$D>150000

-> When evaluated to true, the row in column D will be highlighted pink. 

Supported Operations

The following operators can be used in formula expressions in Analytics

• Operators: +, -, *, /, %, ^, |, &

• Comparators: <, >, <=, >=, <>, !=, =

• Logic: IF, AND, OR, NOT, SWITCH

• Date: TODAY, DATE, RELATIVEDATE, DATETRUNC, DATEPART

• Numeric: MIN, MAX, SUM, AVG, COUNT, ROUND, ROUNDDOWN, ROUNDUP

• String: LEFT, RIGHT, MID, LEN, FIND, SUBSTITUTE, CONCAT, CONTAINS

• Math functions: ACOS, ACOSH, ASIN, ASINH, ATAN, ATAN2, ATANH, CBRT, COS, COSH, ERF, ERFC, EXP, FREXP, GAMMA, HYPOT, LDEXP, LOG, LOG10, LOG2, SIN, SINH, SQRT, TAN, TANH
  

Notable Functions

ABS

ABS(value)

Returns the absolute value of a number "value".

IF

IF(condition, true_value, false_value)

One special application of the IF() function is to provide default values for any blank values. For example, IF(A, A, 42) will return 42 if A is blank/undefined.

IFERROR

IFERROR(test_value[, fallback_value])

If there are any expressions that may possibly throw an error, the IFERROR() can catch that error by using the possibly troublesome expression as the test_value and returning either the value passed in as fallback_value or a blank value if fallback_value is not provided.

CONTAINS

CONTAINS(value, column)

The CONTAINS function can be used to search for string or number (without formatting) in a text or number cell. It uses two arguments, the value and the column that you'd like to search in. For example: CONTAINS('customer', $B) means any row in column B (in this case column B is a text column) that contains a value of 'customer'. Note that this is case-sensitive.

CASE/SWITCH statement

Common in programming languages, the switch statement is useful when the user wants to return specific values or perform specific operations based on what a variable/expression returns (e.g. based on a variable that returns a currency code, we want our formula to use a different currency conversion rate).

Syntax Example:

CASE B WHEN 'USD' THEN A * 0.75 WHEN 'JPY' THEN A * 81.93 WHEN 'EUR' THEN A * 0.68 ELSE A END

The above example shows how to use the switch statement. Given an expression for CASE, it will try to find a match for the expression in each WHEN, executing the THEN clause of the first match that it finds, ignoring everything else. Otherwise, if the expression doesn’t match any of the values defined in each WHEN, it will default to the ELSE clause.

Another way of writing the switch statement that is also supported is:

Alternative Syntax Example:

SWITCH(B, 'USD', A * 0.75, 'JPY', A * 81.93, 'EUR', A * 0.68, A)

TODAY

TODAY()

Returns today’s date. Can be used to implement rolling data windows when using in Filters.

RELATIVEDATE

RELATIVEDATE(start_date, relative_number, relative_unit)

Start_date must be a special Date object, which can be returned through functions such as TODAY, RELATIVEDATE, DATE, and DATETRUNC.

Relative_number is an integer and can be a positive or negative number.

Relative_unit must be one of the following: 'DAY', 'WEEK', 'MONTH', 'YEAR'.  Defaults to 'DAY' if it's not provided.

Syntax Example:

RELATIVEDATE(TODAY(), -5, 'YEAR')

The example above will return a Date 5 years prior to today. If today’s date is July 15, 2020, it will return July 15, 2015.

DATE

DATE([date_string])

Returns a Date object represented by date_string. If no date_string is provided, it acts the same as TODAY.

DATETRUNC

DATETRUNC(date, date_part)

Truncates date to accuracy specified by date_part.

Date must be a special Date object, which can be returned through functions such as TODAY, RELATIVEDATE, DATE, DATETRUNC.

Date_part must be one of the following: 'DAY', 'WEEK', 'MONTH', 'YEAR'. Defaults to 'DAY' if not provided.

Syntax Example:

DATETRUNC(TODAY(), 'YEAR')

The above example will return a date representing today but only keeping the year information (i.e. the beginning of the current year). If today’s date is July 15, 2020, it will return the year 2020.

DATEPART

DATEPART(date, date_part)

Returns a whole number representing the extracted part of the date specified by date_part.

Date must be a special Date object, which can be returned through functions such as TODAY, RELATIVEDATE, DATE, DATETRUNC.

Date_part must be one of the following: 'DAY', 'WEEK', 'MONTH', 'YEAR'. Defaults to 'DAY' if not provided.

Syntax Example:

DATEPART(TODAY(), 'YEAR')

The above example will return a whole number representing the current year. If today’s date is July 15, 2020, it will return 2020.