Zoomdata Version

Support for Calculations

Different calculation types support different types of functions and certain data sources support only certain types of functions. To determine what functions you can use in your calculations, refer to the following tables.

Calculation type Data source type Functions supported
Operators All All

Aggregations

(Column, Table, Window)

All All
Derived fields Impala, PostgreSQL, SAP Hana See table below

 

General Operators

Mathematical

Operator Description Supported By:
+ (ADD) Addition: adds two numbers All data sources
- (SUBTRACT) Subtracts one numeric value from another All data sources
* (MULTIPLY) Multiply one number by another All data sources
/ (DIVIDE) Divide one number by another All data sources

 

Logical

Operator Description Supported By:
AND Evaluates to TRUE if both boolean expressions are TRUE All data sources
OR Evaluates to TRUE if either boolean expression is TRUE All data sources
NOT Reverses the value of any other Boolean operator All data sources
IN Evaluates to TRUE if the operand is equal to one of a list of expressions. All data sources
BETWEEN Evaluates to TRUE if the operand is within a range All data sources

 

Relational

Operator Description Supported By:
= Checks if the values of two operands are equal or not, if yes then condition becomes true All data sources
!= Checks if the values of two operands are equal or not- if values are not equal then condition becomes true All data sources
> Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true All data sources
< Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true All data sources
>= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true All data sources
<= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true All data sources

Supported Aggregations

Aggregations operate on columns, whole tables, or parts of tables. For information about using aggregations to create custom calculations, see Creating Calculations and Derived Fields.

Supported Aggregations
Function Type Description

SUM ( FieldID ),

TableSUM( FieldID ),

WindowSUM( FieldID, list of attributes )

numeric

Return the sum of included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

AVG( FieldID ),

TableAVG( FieldID ),

WindowAVG( FieldID, list of attributes )

numeric

Return the average of included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

MIN( FieldID ),

TableMIN( FieldID ),

WindowMIN( FieldID, list of attributes )

numeric

Return the minimum value in the included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

MAX( FieldID ),

TableMAX( FieldID ),

WindowMAX( FieldID, list of attributes )

numeric

Return the maximum value in the included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

COUNT( FieldID ),

TableCOUNT( FieldID ),

WindowCOUNT( FieldID, list of attributes )

any

Return the number of included values. When a FieldID is specified, these functions ignore null values. You can use the wildcard character (*) for FieldID, in which case these functions include null values found in your data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

COUNTD( FieldID ),TableCOUNTD( FieldID ),

WindowCOUNTD( FieldID, list of attributes )

any

Return the number of unique values within the included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

Derived Fields

You can use derived fields on dashboards created from Impala and PostgreSQL data sources. For examples on how and when to use derived fields, see Creating Calculations and Derived Fields.

Numerical Functions

Function Description Example
ROUND Rounds a numeric value to the number of decimals specified ROUND (Field_A, 0)
FLOOR Returns the largest integer value that is not greater than the passed value FLOOR (value : Numeric) : Numeric
CEIL Returns the smallest integer value that is not less than the passed Value CEIL (value : Numeric) : Numeric
ABS Returns the absolute, positive value of a given numeric expression ABS (value : Numeric) : Numeric
POWER Returns the Base raised to the Exponent~th power POWER (Field_A * 123.45, Field_B / 3)
MOD Returns the remainder of a division of dividend by divider MOD (Field_A * 123.45, Field_B / 3)
SQRT Returns the non-negative square root of numeric expression SQRT (Field_A * 123.45)
EXP Returns the base of the natural logarithm (e) raised to the power of passed numeric expression EXP (Field_A * 123.45)
LN Returns the natural logarithm (base e) of the specified numeric expression LN (Field_A * 123.45)
NUM_TO_TEXT_ROUNDED Converts the numeric expression rounded to the number of decimals specified to the text NUM_TO_TEXT_ROUNDED (Field_A * 123.45, Field_B / 3
NUM_TO_TEXT Converts the numeric expression to text NUM_TO_TEXT (value : Numeric)
UNIX_TIME_TO_TIME Converts the numeric expression to time UNIX_TIME_TO_TIME (Field_Milliseconds / 1000)

 

Text Functions

Function Description Example
SUBSTRING Returns the substring of String value which begins at position defined by Start and is Length characters long. SUBSTRING (Field_A, 4, 3)
CONCAT Returns a text that is the result of concatenating two or more Text values CONCAT (Field_FirstName, " ,", Field_LastName)
UPPER Returns the argument in uppercase UPPER (SUBSTRING (Field_A, 0, 3 )
LOWER Returns the argument in lowercase LOWER (SUBSTRING (Field_A, 0, 3 ))
LTRIM Returns a text value after removing leading blanks LTRIM (SUBSTRING (Field_A, 0, 5))
RTRIM Returns a text value after removing trailing blanks RTRIM (SUBSTRING (Field_A, 0, 5))
LPAD Returns the text argument, left-padded with the text specified by padString to a length of Length characters LPAD (SUBSTRING (Field_A, 0, 15), 3, 'abc')
RPAD Returns the Text argument, right-padded with the text specified by padString to a length of Length characters RPAD (SUBSTRING (Field_A, 0, 15), 3, 'abc')
REPLACE_ALL Replaces the string with all occurrences of the text specified by fromString replaced by the text defined by toString REPLACE_ALL (SUBSTRING (Field_A, 0, 15), '$', 'USD')
LENGTH Returns the number of characters of the specified string LENGTH (SUBSTRING ("$12456.00", 2, 10)
LOCATE Finds the first occurrence of substring in a string, starting at position LOCATE ('Mr.', CONCAT (Field_FirstName, " ,", Field_LastName), 0)
TEXT_TO_TIME Converts the text expression to time according to the specified format TEXT_TO_TIME (Field_A, "YYYY")
TEXT_TO_NUM Converts the text string to numeric TEXT_TO_NUM (LTRIM (Field_A, '$'))

 

Time Functions

Function Description Example
TIME_ADD Adds an interval value to the TimePart of the DateTime TIME_ADD (hour, +7, field_date)
EXTRACT Extracts the date part of the time EXTRACT (day_of_week, NOW())
TRUNCATE_TIME Rounds (Truncates) the DateTime value down to the granularity specified by TimePart TRUNCATE_TIME (day, NOW())
TIME_TO_UNIX Returns the value of DateTime as a Unix timestamp TIME_TO_UNIX_TIME (NOW())

Was this topic helpful?