Easy Pivot Logo
  • Pricing

Manual

OverviewFolder SystemData Sources
Database DatasetText DatasetHTTP DatasetCalculate ColumnLOD ExpressionsUnified Functions
User and RoleRestful Interfaces

Integration

Dataset

Unified Functions

Unified Functions for different databases

Background Information

We know that different database functions have some variations in syntax, commonly referred to as SQL dialects. The unified calculation functions provided by the BI platform act as a middle-layer translation, shielding underlying database dialect differences.

When creating Calculated Fields or Summary Expressions, you can use the system's built-in functions provided by the BI platform or use native database functions.

System Built-in Functions: This article will detail the definition and usage of each function with comprehensive examples. Please refer to the system built-in functions section.

Native Database Functions: Native functions provided by the underlying database. Different databases have different native functions. If needed, you can consult the respective official documentation for function usage.

Functions

Unified calculation functions were introduced in BI version 1.16. They support all data sources (including Text and HTTP types) except ElasticSearch and MongoDB. For relational databases, mainstream database types are supported, listed below:

  • MySQL
  • Doris
  • H2
  • Clickhouse
  • Oracle
  • Dameng
  • PostgreSQL
  • SQLServer
  • Presto

Note

If you encounter incorrect function translations for the database types listed above, please contact us.

Supported Scenarios

You can use built-in functions in the following scenarios

  • Dataset Calculate Fields (including LOD FIX)
  • Dataset Summary Expressions (including dimensions and aggregated measures defined in LOD INCLUDE / LOD EXCLUDE)
  • Script-type derived dimension fields

Unsupported Scenarios

Using unified calculation functions directly in dataset definition queries is not currently supported.

Logical Functions

Why use logical calculations? Logical calculations allow you to determine whether a specific condition is true or false (Boolean logic). For example, you might want to classify values based on certain conditions.

A logical calculation might look like this:

CASE
    WHEN gender='F' THEN sales
    ELSE 0
END

CASE WHEN

Syntax
CASE  WHEN condition1 THEN result1  WHEN condition2 THEN result2  [ELSE default]END
DefinitionOutputs a value based on WHEN condition evaluation. If none of the WHEN conditions are met, returns the (optional) default value. Returns Null if no default value exists.
OutputDepends on the data type of the THEN values. Note that most databases require consistent output data types across different branches; otherwise, runtime syntax errors may occur.
Example
CASE  WHEN `order_rn` = 1 THEN `order_profit`  ELSE nullEND
CASE  WHEN `gender` = 'F' THEN 1  ELSE 0END

Caution

Note that most databases require consistent output data types across different branches; otherwise, runtime syntax errors may occur.

IF

Syntax
IF(test, then, else)
DefinitionTests if the test condition is true. If true, returns then; otherwise, returns else. Can be seen as a binary branch of CASE-WHEN.
OutputDepends on the data type of the then value.
Example
IF(`order_rn` = 1, `order_profit`, null)
IF(`gender` = 'F', 1, 0)

Caution

Note that most databases require consistent output data types across different branches; otherwise, runtime syntax errors may occur.

Mathematical Functions

ROUND

Syntax
ROUND(number, [precise])
DefinitionRounds [number] to the specified number of decimal places. The optional precise parameter specifies the precision (number of decimal digits) to include in the final result. If decimals is omitted, number is rounded to the nearest integer.
Example
ROUND(1/3, 2) = 0.33

FLOOR

Syntax
FLOOR(number)
DefinitionAs the name suggests, Floor in English means floor. Rounds [number] down to the nearest integer. Corresponding functions are CEIL (round up) and ROUND.
Example
FLOOR(7.9) = 7

CEIL

Syntax
CEIL(number)
DefinitionAs the name suggests, Ceil in English means ceiling. Rounds [number] up to the nearest integer. Corresponding functions are FLOOR (round down) and ROUND.
Example
CEIL(7.1) = 8

ABS

Syntax
ABS(number)
DefinitionReturns the absolute value of the argument number.
Example
ABS(-7) = 7

GREATEST

Syntax
GREATEST(number1, number2,...)
DefinitionReturns the greatest value among multiple arguments. The opposite function is LEAST.
Example
GREATEST(1, 2, 3) -- Output: 3

LEAST

Syntax
LEAST(number1, number2,...)
DefinitionReturns the smallest value among multiple arguments. The opposite function is GREATEST.
Example
LEAST(1, 2, 3) -- Output: 1

DIVIDE

Syntax
DIVIDE(number1, number2, [nullValue])
DefinitionDivision A/B, compatible with divisor being 0, solving the issue where direct A/B in databases throws an error when the divisor is 0. Returns [nullValue] when the divisor is 0. If no nullValue is provided, returns Null.
Example
DIVIDE(3, 2) -- Output: 1.5
DIVIDE(3, 0, 0) -- Output: 0

CHGRATE

Syntax
CHGRATE(from, to, [nullValue])
DefinitionCalculates the change rate from [from] to [to]: (to - from) / from
Compatible when from is 0. Returns [nullValue] when from is 0. If no nullValue is provided, returns Null.
Example
CHGRATE(2, 3) -- Output: 0.5
CHGRATE(0, 2, 0) -- Output: 0

String Functions

CONCAT

Syntax
CONCAT('a', ',', 'b')
DefinitionString concatenation function.
Example
CONCAT('a', ',', 'b') -- Output: a,b

LOWER

Syntax
LOWER(string)
DefinitionConverts a string to lowercase.
Example
LOWER('ABc') -- Output: 'abc'

UPPER

Syntax
UPPER([string])
DefinitionConverts a string to uppercase.
Example
UPPER('ABc') -- Output: 'ABC'

LPAD

Syntax
LPAD(string, length, pad_string)
DefinitionLeft-pads the string with pad_string when string length is less than length.
Example
LPAD('abc', 5, ' ') -- Output: '  abc'

RPAD

Syntax
RPAD(string, length, pad_string)
DefinitionRight-pads the string with pad_string when string length is less than length.
Example
RPAD('abc', 5, ' ') -- Output: 'abc  '

TRIM

SyntaxTRIM(string)
DefinitionRemoves leading and trailing spaces from string.
Example
TRIM(' abc ') -- Output: 'abc'

FIND

SyntaxFIND(string, substring)
OutputInteger
DefinitionFinds the position of substring substring within original string string. Returns 0 if the substring is not found. The position of the first character in the string is 1. Returns null if the first argument is null.
Example
FIND('Hello how are you', 'how') -- Output: 7
FIND('Hello how are you', 'what') -- Output: 0
FIND(null, 'how') -- Output: NULL

CONTAINS

SyntaxCONTAINS(string, substring)
OutputBoolean
DefinitionChecks if string string contains substring substring.
Example
CONTAINS('Hello how are you', 'How') -- Output: true

ENDSWITH

SyntaxENDSWITH(string, substring)
OutputBoolean
DefinitionReturns true if the given string ends with the specified substring.
Example
ENDSWITH('Hello how are you', 'you') -- Output: true

STARTSWITH

SyntaxSTARTSWITH(string, substring)
OutputBoolean
DefinitionReturns true if the given string starts with the specified substring.
Example
STARTSWITH('Hello how are you', 'He') -- Output: true

LENGTH

SyntaxLENGTH(string)
OutputInteger
DefinitionReturns the length of string string.
Example
LENGTH('Hello') -- Output: 5

REPLACE

SyntaxREPLACE(string, substring, replacement)
OutputString
DefinitionReplaces substring in string with replacement.
Example
REPLACE('Version 3.8', '3.8', '4x') = "Version 4x" -- Output: "Version 4x"

SPLIT

SyntaxSPLIT(string, delimiter, token number)
OutputString
DefinitionReturns a substring from the string, using the delimiter character to split the string into a sequence of tokens.
Example
SPLIT ('a-b-c-d', '-', 2) = "b"
Note

Some data sources have limitations when splitting strings. When the token number exceeds the number of split substrings, some databases return null, while others return the last string.

Date Functions

DATEADD

SyntaxDATEADD(date, interval, dateunit)
OutputDate
DefinitionAdds the specified number [interval] of date units [dateUnit] to the [date] parameter. DateUnit supports: 'year', 'quarter', 'month', 'day', 'week', 'hour', 'minute', 'second'
Example
DATEADD(`birth_day`, 1, 'year') Adds 1 year to the birth date

ADDYEAR

SyntaxADDYEAR(date, interval)
OutputDate
DefinitionAdds the specified number [interval] of years to the [date] parameter. If interval is negative, subtracts. Equivalent to DATEADD(birth_day, 1, 'year').
Example
ADDYEAR(`dt`, -1) Gets the date corresponding to 1 year before `dt`

ADDMONTH

SyntaxADDMONTH(date, interval)
OutputDate
DefinitionAdds the specified number [interval] of months to the [date] parameter. If interval is negative, subtracts. Equivalent to DATEADD(birth_day, 1, 'month').
Example
ADDMONTH(`dt`, -1) Gets the date corresponding to 1 month before `dt`

ADDDAY

SyntaxADDDAY(date, interval)
OutputDate
DefinitionAdds the specified number [interval] of days to the [date] parameter. If interval is negative, subtracts. Equivalent to DATEADD(birth_day, 1, 'day').
Example
ADDDAY(`dt`, -1) Gets the date corresponding to 1 day before `dt`

ADDWEEK

SyntaxADDWEEK(date, interval)
OutputDate
DefinitionAdds the specified number [interval] of weeks to the [date] parameter. If interval is negative, subtracts. Equivalent to DATEADD(birth_day, 1, 'week').
Example
ADDWEEK(`dt`, -1) Gets the date corresponding to 1 week before `dt`

ADDHOUR

SyntaxADDHOUR(date, interval)
OutputDateTime
DefinitionAdds the specified number [interval] of hours to the [date] parameter. If interval is negative, subtracts. Equivalent to DATEADD(birth_day, 1, 'hour').
Example
ADDHOUR(`dt`, -1) Gets the date time corresponding to 1 hour before `dt`

DATEDIFF

SyntaxDATEDIFF(dateUnit, startDate, endDate)
OutputInteger
DefinitionReturns the difference between [startDate] and [endDate] expressed in units of [dateUnit]. DateUnit supports: 'year', 'quarter', 'month', 'day', 'week', 'hour', 'minute', 'second'
Example
DATEDIFF('year', `BIRTH_DATE`, CURRENT_DATE()) -- Output: age

DATETRUNC

SyntaxDATETRUNC(dateUnit, date)
OutputDate
DefinitionDate truncation. Returns the date DATETRUNC(dateUnit, date). DateUnit supports: 'year', 'quarter', 'month', 'day', 'week', 'hour', 'minute', 'second'
Example
DATETRUNC('year', `BIRTH_DATE`) -- Output: Date('1985-01-01')

DATEPART

SyntaxDATEPART(datePart, date)
OutputInteger
DefinitionGets the name of the date part DATEPART(datePart, date). DatePart supports: 'year', 'month', 'dayofyear', 'dayofmonth', 'dayofweek', 'isodayofweek', 'weekofyear'
Example
DATEPART('year', `BIRTH_DATE`) -- Output: 1985
NoteIt is more recommended to use specific date part functions like dayOfMonth(date), YEAR(date) to get date parts.

YEAR

SyntaxYEAR(date)
OutputInteger
DefinitionReturns the year of the given [date] as an integer.
Example
YEAR(`BIRTH_DATE`) -- Output: 1985

MONTH

SyntaxMONTH(date)
OutputInteger
DefinitionReturns the month of the given [date] as an integer.
Example
MONTH(`BIRTH_DATE`) -- Output: 1-12

DAYOFMONTH

SyntaxDAYOFMONTH(date)
OutputInteger
DefinitionReturns the day of the month (day of month) of the given [date] as an integer.
Example
DAYOFMONTH(`BIRTH_DATE`) -- Output: 1-31

QUARTER

SyntaxQUARTER(date)
OutputInteger
DefinitionReturns the quarter of the given [date] as an integer.
Example
QUARTER(`BIRTH_DATE`) -- Output: 1-4

DAYOFWEEK

SyntaxDAYOFWEEK(date)
OutputInteger
DefinitionReturns the day of the week of the given [date] as an integer.
Example
DAYOFWEEK(`BIRTH_DATE`) -- Output: 1-7
Note

This function does not guarantee consistency due to database differences; the week count returned may start with Monday or Sunday as the first day. The related function ISODAYOFWEEK returns the week starting with Monday as the first day.

ISODAYOFWEEK

SyntaxISODAYOFWEEK(date)
OutputInteger
DefinitionReturns the day of the week of the given [date] as an integer (Monday as the first day).
Example
ISODAYOFWEEK(`BIRTH_DATE`) -- Output: 1-7

WEEKOFYEAR

SyntaxWEEKOFYEAR(date)
OutputInteger
DefinitionReturns the week number of the year of the given [date] as an integer.
Example
WEEKOFYEAR(`BIRTH_DATE`) -- Output: 17

STRING2DATE

SyntaxSTRING2DATE(dateString, dateFormat)
OutputDate
DefinitionConverts a string to a date.

Date part List
yyyy: Year(2022)
yy: Short Year(22)
MM: Month(01-12)
dd: Day of Month(01-31)
HH: Hour (00..23)
ww: week of year(01..53), Monday is first day of week
Example
STRING2DATE('2024-10-01', 'yyyy-MM-dd') -- Output: Date('2024-10-01')

DATE_FORMAT

SyntaxDATE_FORMAT(date, dateFormat)
OutputString
DefinitionConverts a date to a date string in the specified format.

Date part List
yyyy: Year(2022)
yy: Short Year(22)
MM: Month(01-12)
dd: Day of Month(01-31)
HH: Hour (00..23)
ww: week of year(01..53), Monday is first day of week
Example
DATE_FORMAT(CURRENT_DATE(), 'yyyy-MM') -- Output: '2024-10'

NOW

SyntaxNOW()
OutputCurrent date + time
DefinitionCurrent date + time
Example
NOW() -- Output: 2024-10-21T10:33:37

CURRENT_DATE

SyntaxCURRENT_DATE()
OutputCurrent date
DefinitionCurrent date
Example
CURRENT_DATE() -- Output: DATE('2024-10-21')

LOD Expressions

Level Of Detail Expressions

Insight

Design Pivot Table / Chart

On this page

Background InformationFunctionsSupported ScenariosLogical FunctionsCASE WHENIFMathematical FunctionsROUNDFLOORCEILABSGREATESTLEASTDIVIDECHGRATEString FunctionsCONCATLOWERUPPERLPADRPADTRIMFINDCONTAINSENDSWITHSTARTSWITHLENGTHREPLACESPLITDate FunctionsDATEADDADDYEARADDMONTHADDDAYADDWEEKADDHOURDATEDIFFDATETRUNCDATEPARTYEARMONTHDAYOFMONTHQUARTERDAYOFWEEKISODAYOFWEEKWEEKOFYEARSTRING2DATEDATE_FORMATNOWCURRENT_DATE
Log InStart Free