Formula Functions

General Rules

  • Each open parenthesis must be closed.
  • String expressions can be surrounded by either "" (double quotation marks) or '' (single quotation marks).

Aggregation Functions

The following are the aggregation functions and their descriptions.

  • sum(double vector expression). Returns the sum of a vector expression.
  • sum(double vector expression,groupBy(dimension, ...)). Evaluate the sum of a vector expression based on a specific aggregation level. It is important to include all the leading grouping dimension(s) in the Insight from the top down to the level based measure to be evaluated.
  • max(double [] expression). Returns the maximum of a vector expression.
  • max(double vector expression,groupBy(dimension, ...)). Evaluate the maximum of a vector expression based on a specific aggregation level. It is important to include all the leading grouping dimension(s) in the Insight from the top down to the level based measure to be evaluated.
  • min(double [] expression). Returns the minimum of a vector expression.
  • min(double vector expression,groupBy(dimension, ...)) Evaluate the minimum of a vector expression based on a specific aggregation level. It is important to include all the leading grouping dimension(s) in the Insight from the top down to the level based measure to be evaluated.
  • median(double [] expression). Returns the median of a vector expression.
  • median(double vector expression,groupBy(dimension, ...)). Evaluate the median of a vector expression based on a specific aggregation level. It is important to include all the leading grouping dimension(s) in the Insight from the top down to the level based measure to be evaluated.
  • average(double [] expression). Returns the average of a vector expression.
  • average(double vector expression,groupBy(dimension, ...)). Evaluate the average of a vector expression based on a specific aggregation level. It is important to include all the leading grouping dimension(s) in the Insight from the top down to the level based measure to be evaluated.
  • count(object [] expression). Returns the count of a vector expression.
  • count(double vector expression,groupBy(dimension, ...)). Evaluate the count of a vector expression based on a specific aggregation level. It is important to include all the leading grouping dimension(s) in the Insight from the top down to the level based measure to be evaluated.
  • distinct(object [] expression). Returns the count of distinct values of a vector expression.
  • distinct(double vector expression,groupBy(dimension, ...)). Evaluate the distinct of a vector expression based on a specific aggregation level. It is important to include all the leading grouping dimension(s) in the Insight from the top down to the level based measure to be evaluated.

Boolean Functions

The following are Boolean functions and their descriptions.

  • and(bool exp1, bool exp2, ...) This boolean function takes one (or multiple) boolean expressions and returns “true” if all are satisfied, otherwise it returns “false”.

    • Example: and(SALES.SALES.AMOUNT_SOLD=3696.48, SALES.SALES.COST_OF_GOODS=1670.79, SALES.COUNTRIES.COUNTRY_NAME='Argentina'). This formula returns “true” where the revenue, cost, and country name columns are of specific values. If one of these values are not matched, it returns “false”.
  • contains(field exp, find string). This function checks if a field expression (first parameter) ends with a given string (second parameter) , returning “true” if it is, otherwise it returns “false”. It is not case-sensitive.

    • Example: contains(SALES.COUNTRIES.COUNTRY_NAME, "n"). This formula returns “true” if a record in the country name column contains the letter “n” (e.g. Spain), otherwise, it returns “false”.
  • endsWith(string value, string pattern). This function checks if a string value (first parameter) ends with a given string pattern (second parameter) to return “true” if it is, otherwise it returns “false”. It is not case-sensitive.

    • Example: endsWith(SALES.COUNTRIES.COUNTRY_NAME,"n"). This formula returns “true” if a record in the country name column ends with an “n” (e.g. Spain), otherwise, it returns “false”.
  • in(find exp, const1, const2, ...). This function takes an expression (column) and returns “true” if it is equal to one (or more) constants, otherwise it returns “false”.

    • Example: in(SALES.COUNTRIES.COUNTRY_NAME, "Germany", "Spain"). This function returns “true” if a record in the country name column is either “Germany” or “Spain”, otherwise it returns “false”.
  • inList(find exp, list). This function takes in an expression and a comma-separated list and returns “true” if any of the list items match the given expression. It could also compare a column against a variable containing a large list of data. It can be used to define a data security filter either in the Table Details page or in an insight.

    • Example: inList(SALES.COUNTRIES.COUNTRY_NAME, "Spain, Germany"). This function returns “true” each time a record of the country name column is either “Spain” or “Germany”, otherwise it returns “false”.
  • isNull(fied exp). This function takes a field expression and returns “true” if it is null, otherwise it returns “false”.

    • Example: isNull(SALES.COUNTRIES.COUNTRY_NAME). This function takes a column and returns “true” for each null record, otherwise it returns “false”.
  • not(bool exp). This function takes a boolean expression and returns “true” if it is false.

    • Example: not(SALES.COUNTRIES.COUNTRY_NAME = 'Germany'). This function returns “true” for any record in the country name column but “Germany”, otherwise it returns “false”.
  • or(bool exp1, bool exp2, ...). This boolean function takes one (or multiple) boolean expressions and returns “true” if one (or all) is satisfied, otherwise it returns “false”.

    • Example: or(SALES.SALES.AMOUNT_SOLD<3600, SALES.COUNTRIES.COUNTRY_NAME='Germany'). This boolean function returns “true” if the revenue is less than 3600, or the country name column is “Germany”. If none of these conditions is satisfied, it returns “false” .
  • startsWith(field exp, find string). This function checks if a field expression (first parameter) starts with a given string (second parameter). to return “true” if it is, otherwise it returns “false”. It is not case-sensitive.

    • Example: startsWith(SALES.COUNTRIES.COUNTRY_NAME, 's'). This function returns “true” if a country starts with the letter “s”, otherwise it returns “false”.

Conditional Statements

The following is a conditional statement.

case(bool exp1,result1,bool exp2,result2,..., else). The “case” statement has the same functionality of the “if” statement. For example:

case(SALES.COUNTRIES.COUNTRY_NAME = 'Spain', 'Great', SALES.COUNTRIES.COUNTRY_NAME = 'Argentina', 'Cool', 'Other_Country')

In this example, for rows with “Spain” in the country name column, the case statement would return the word “Great”. For “Argentina”, it would return “Cool”, otherwise, it would return “Other_Country”. if(bool exp, expression1, expression2) The “if” statement returns the first expression if the boolean expression is “true”, otherwise it returns the second expression. If the first and second expressions are numbers, the result will be of type double.

if(SALES.COUNTRIES.COUNTRY_NAME = 'Spain', 'Great', 'Other_Country')

In this example, for rows with “Spain” in the country name column, the if statement would return the word “Great”. For other country names, it would return the word “Other_Country”.

Conversion Functions

The following are conversion functions and their descriptions.

  • epoch(int exp). The epoch or ‘Unix Time’ is the time elapsed since January 1, 1970. This function can be used to return the number of seconds elapsed since January 1, 1970 until a specified date.

    • Example: epoch(SALES.Sales_Date_dim.Sales_Date). This function returns the number of seconds elapsed from January 1, 1970 until the date of each record in the Sales_Date column.
  • int(double exp). Converts a double expression into an integer.

    • Example: int(2306.77). Converts the double into an integer to equal 2307.
    • Example: int(SALES.SALES.AMOUNT_SOLD). Converts each record into an integer.
  • long(double exp). Converts a double expression into a long.

    • Example: long(SALES.SALES.AMOUNT_SOLD)
  • parseDate(string exp, string format_mask). This function takes in a string date representation to Convert it into an equivalent value of a date type in the same format as the given format_mask.

    • Example: parseDate(INCORTA.sales.TIME_ID_String,"dd/MM/yy"). This function takes in a string date expression column in the dd/MM/yyyy format and returns a date expression in the format dd/MM/yyyy.
  • parseDouble(string exp, string mask). Converts a string representation of a number into a double specified by a Java DecimalFormat mask.

    • Example: parseDouble("12,345", "#,##0.0#;(###0.#)) returns 12,345.00.
  • parseTimestamp (string exp, string mask). This function takes in a string date (the first parameter), whose format has to match the string mask (the second parameter) and returns it as a date type in a format matching the logged-in user locale preferences. The function takes any valid java mask. The returned date format is not affected by the incoming data, or the string mask. To change the returned date format, use the formatDate(date expression, string mask) function.

    • Example: parseTimestamp ("2001-12-24 23:05:33.000", "yyyy-MM-dd HH:mm:ss.SSS"). This function converts the string representation of the timestamp into a date type of a matching format to that of the logged-in user locale preferences.
  • string(int exp). Converts an integer expression into a string.

    • Example: string(INCORTA.SALES.AMOUNT_SOLD). This function converts the integer records of the amount_sold column into a string.
  • timestamp(string exp) Converts a string timestamp representation into a date type. The string expression must be in the format “yyyy-MM-dd HH:mm:ss.SSS”. The date is returned in a format according to the logged-in user locale preferences. To change the returned date format, use the formatDate(date expression, string mask) function.

    • Example: timestamp ("2001-12-24 23:55:12.000"). This function converts the string representation of the timestamp into a timestamp field.

Filter Functions

The following are filter functions and their descriptions.

  • firstVersion(value, group-by, order-by). Returns the first occurrence of a value with respect to date (i.e. the order-by parameter). Unless used as a filter, this function must be used inside an aggregation function (e.g. sum, min, max, avg).

    • Example: sum(firstVersion(SALES.SALES.AMOUNT_SOLD, SALES.SALES.CUSTOMER_ID, SALES.SALES.TIME_ID)). This function returns the total of the earliest generated revenue by each customer.
  • lastVersion(value, group by, order by). Returns the last occurrence of a value with respect to date (i.e. the order-by parameter). Unless used as a filter, this function must be used inside an aggregation function (e.g. sum, min, max, avg.)

    • Example: sum(lastVersion(SALES.SALES.AMOUNT_SOLD, SALES.SALES.CUSTOMER_ID, SALES.SALES.TIME_ID)). This function returns the total of the latest generated revenue by each customer.

Miscellaneous Functions

The following are miscellaneous functions and their descriptions.

  • descendantOf(field, $sessionVariable). This boolean function takes a column of a hierarchical table as the first parameter, and returns “true” for each row where the first parameter is descendant of the second one. The “value” parameter could be a value (e.g. Joe), or variable (e.g. $employee).

    • Example: descendantOf(HR.EMPLOYEES.FIRST_ NAME, “John”). This function returns “true” for each record (in the “FIRST_NAME” column) that is descendant of “John”, i.e. whose manager is “John”.
    • Example: descendantOf(HR.EMPLOYEES.FIRST_ NAME, “John”). You can also create a variable for the second parameter to compare against the records of the first one. When creating the variable, you can give it a default value, which users can change on the Insight level.
  • descendantOf(field, $sessionVariable, depth). This boolean function takes a column of a hierarchical table as the first parameter, and returns “true” for each row where the first parameter is descendant of the second one, down to the depth level specified by the “depth” parameter. The “$sessionVariable” parameter could be a value (e.g. Joe), or variable (e.g. $employee).

    • Example: descendantOf(HR.EMPLOYEES.FIRST_ NAME, “John”, 2). If John is the manager of Paul, and if Paul is the manager of Mary, and if Mary is the manager of Dan, the function will return “true” for Paul and Mary only. The function will return “false” for Dan, since Dan is at depth level 3.
    • Example: descendantOf(HR.EMPLOYEES.FIRST_ NAME, $Emp, 2). You can also create a variable for the second parameter to compare against the records of the first one. When creating the variable, you can give it a default value, which users can change on the Insight level.
  • lookup(result lookup field, primary key field 1, primary key value 1, primary key field 2, primary key value 2, ..., default value). This function fetches a value from a table using primary key values. The first parameter is the field value to be returned, while the remaining parameters are the table primary-key fields and their respective values. The last parameter takes in the default value, which could be through a presentation variable.

    • Example: lookup(SALES.CUSTOMERS.CUST_LAST_NAME, SALES.CUSTOMERS.CUST_ID, SALES.SALES.CUSTOMER_ID). This example returns customers last name whose customer IDs found in the CUSTOMERS table match those found in the SALES table.
  • schemaRefreshTime (string schemaName) This function takes a schema name and returns the last refresh start time if the system is running. If the system is rebooted, the last load start time gets returned (until the next refresh). If the schema is not part of a dashboard, it will not be accurate and will show the date/time the dashboard first ran and the function had been called.

    • Example: schemaRefreshTime ('SALES'). This function returns the last refresh start time for the “SALES” schema.

Scalar Arithmetic Functions

The following are scalar arithmetic functions and their descriptions.

  • abs(double expression). Returns the absolute (no negative sign) value of a double expression. abs(-4) = 4
  • ceil(double exp). Returns the smallest integer that is greater than, or equal to, an argument.ceil(5.9) = 6
  • floor(double exp). Returns the largest integer that is less than, or equal to, the argument.floor(5.9) = 5
  • max(double expression, double expression). Returns the maximum of two double expressions.max(2.5, 3.5) = 3.5
  • min(double expression, double expression). Returns the minimum of two double value expressions.min(2.5, 3.5) = 2.5
  • mod(double dividend, double divisor). Returns the remainder of dividing a numerical dividend by a numerical divisor.
  • rnd() Returns a random number between 0 and 1.Not applicable since this function generates a different random number each time it is used.
  • round(double expression). Returns the closest long. round(14.5) = 15. round(14.4) = 14.round(Sales.Sales.Amount_Sold) = <ROUNDED_ROW_VALUE>

Scalar Date Functions

The following are scalar date functions and their descriptions.

There is a known issue with the following date functions, returning random values for null records. As a workaround, use a combination of the “if” and “isNull” functions as shown below:

if(isNull(<DATE_COLUMN>, “<STRING_EXPRESSION>”, string(<FUNCTION>(<DATE_COLUMN>)))

  • addDays(date exp, integer days). This function adds an integer number (+/-) of days to a date given in the format “yyyy-MM-dd”, and returns the final date as a result.

    • Example: addDays(timestamp("2001-12-24"), 4). This function takes a date expression/column and adds 4 to it, returning the resulting date: “2001-12-28”.
  • addMinutes(date exp, long minutes). This function adds a specified number of minutes to a date in the format “yyyy-MM-dd”, and returns the final date as a result.

    • Example: addMinutes(timestamp("2001-12-24"), 10). This function adds 10 minutes to the timestamp and returns “2001-12-25 00:05:12.000”.
  • date(string expression). Converts a string to a date using the “en-US” locale and the format mask “yyyy-MM-dd”.

    • Example: date("2001-12-24"). This function takes a string date representation and returns a date value.
  • day(date expression). This function takes a date expression in the “yyyy-MM-dd” format and returns the month day component of the date/timestamp.

    • Example: day(date("2001-12-24")). This function takes a date expression/column and return the day number of the month, which is 24.
  • daysBetween(date expression, date expression). This function takes two dates in the “yyyy-MM-dd” format and returns the number of days between them. Note the first parameter should be the later date while the second should be the earlier date.

    • Example: daysBetween(timestamp("2001-12-24 23:55:12.000"), timestamp("2001-12-20 23:55:12.000"))). This function returns 4 days, which is the number of days between the two dates.
  • formatDate(date expression, string mask). This function formats a date using any simple java date formatting masks. (e.g. formatDate(<schema.table.column>, "yyyy-MM-dd")), where “yyyy-MM-dd” is the date format to be returned in.

    • Example: formatDate(timestamp("2001-12-24 23:55:12.000"), "MMM-dd-yyyy") This function takes in a date and returns it in the same format as the string mask to be: Dec-24-2001.
  • hour(date expression). Returns the hour component of a date/timestamp. The date should be in the format “yyyy-MM-dd”.

    • Example: hour(timestamp("2001-12-24 23:55:12.000")) The timestamp function converts the string timestamp representation into a timestamp. The “hour” function returns the hour component of the timestamp, which is “23”.
  • ifNull(date exp1, date exp2). Returns the first expression if it is not null, otherwise, the second expression gets returned. Expressions could be of type string, date, or timestamp. There is a known issue with this function. Use the “isNull” function as a workaround as shown at the beginning of this table.
  • minute(date expression). Returns the minute component of the date field/timestamp. The date should be in the format “yyyy-MM-dd”.

    • Example: minute (timestamp("2001-12-24")). The timestamp function converts the string timestamp representation into a timestamp. The “minute” function returns the minute component of the timestamp, which is “55”.
  • month(date expression). Returns the month component of a date/timestamp. The date should be in the format “yyyy-MM-dd”.

    • Example: month(INCORTA.EMP.DATE_CREATED). This function takes in a date expression and returns the month of that date.
  • removeTime(Timestamp expression). Removes the time component from a timestamp, returning only the date.

    • Example: removeTime(INCORTA.EMP.DATE_CREATED). This function removes the time component from the timestamp “1/1/00 12:00:00 AM” and returns only the date “1/1/00”.
  • second(date exp). This function takes a date expression in the “yyyy-MM-dd HH:mm:ss” format and returns the seconds component of the date/timestamp.

    • Example: second(timestamp("2001-12-24 23:05:33.000")). This function returns the seconds portion of the given timestamp, “33”.
  • timeBetween(timestamp endTime, timestamp startTime). This function takes in the end time (first parameter) and start time (second parameter), and returns the duration between the two given timestamps in milliseconds.

    • Example: timeBetween(timestamp("2001-12-24 23:05:00.000"), timestamp("2001-12-24 23:00:00.000")). This function returns 300,000 milliseconds, which is 5 minutes.
  • quarter(date expression) This function takes in a date expression in the “yyyy-MM-dd HH:mm:ss” format and returns the quarter corresponding to the date/timestamp.

    • Example: quarter(timestamp("2001-12-24 23:05:33.000")). This function returns the quarter corresponding to the given date/timestamp, which is “4”.
  • weekday(date expression). This function takes in a date expression in the format “yyyy-MM-dd HH:mm:ss” and returns the day number of the week of that date/time stamp. Note that Sunday is considered as the first day of the week.

    • Example: weekday(timestamp("2014-07-07 00:00:00")). This function returns “2” as the day number of the week, i.e. Monday.
  • weeknum(date expression). This function takes in a date expression in the format “yyyy-MM-dd HH:mm:ss” and returns the week number in the year of that date/time stamp.

    • Example: weeknum(timestamp("2014-01-01 00:00:00")). This function returns the week number in the year 2014, which is “1” in this example.
  • year(date expression). Returns the year component of a date/timestamp. The date should be in the format “yyyy-MM-dd HH:mm:ss”.

    • Example: year(timestamp("2001-12-24 23:55:12.000")). The timestamp function converts the string timestamp representation into a timestamp. The “year” function returns the year component of the timestamp, which is “2001”.

Scalar String Functions

The following are scalar string functions and their descriptions.

  • bin(double exp, string c1, double d1, ..., string default)Returns string c1 (second parameter) if a double expression (first parameter) < d1 (third parameter)
else string c2 if exp < d2  
... 
else default string (last parameter.bin(INCORTA.ADMIN_EMP.EMPNO, "low", 4, "high")

This function returns the string “low” for each employee with ID less than 4, otherwise it returns the default string expression “high”.

  • concat(string exp1, string exp2, ...) Returns the concatenation of an arbitrary number of string expressions.

    • Example: concat("San M", "ateo", " Bridge"). This function concatenates the string parameters and returns: “San Mateo Bridge”.
  • find(string exp1, string exp2). This function returns the index number of one string expression (first parameter) in another string expression (second parameter. The first character starts at 0 and is -1 if exp 1 was not found in exp2.

    • Example: find("e", "Hello"). This function returns the index number of the letter “e”, which is “1”.
  • findLast(string exp1, string exp2). This function returns the index number of the last occurrence of one string expression (first parameter) in another string expression (second parameter).

    • Example: The first character starts at 0 and is -1 if exp 1 was not found in exp2.findLast("a", "banana") This function returns the index number of the last occurrence of the letter “a”, which is 5.
  • length(string exp). Returns the length of a string expression, including spaces.

    • Example:length("San Mateo"). This function returns the length of the string expression including spaces, which is “9”.
  • lower(string expression). Returns the same string in lower-case letters.

    • Example: lower("Sally") returns the string “Sally” in lower case to read “sally”.
  • lTrim(string expression). This function takes a string expression and returns it without the leading white space.

    • Example: lTrim(" Hello"). This function trims the leading white space and returns the word “Hello” without the space before it.
  • replace(string exp, string old, string new). Searches string field (first parameter) for specified string characters (second parameter) to replace with other characters (third parameter).

    • Example: replace(INCORTA.ADMIN_EMP.JOB,"Q","Zee"). This function searches the “JOB” column in the ADMIN_EMP table for the occurrence of “Q” to replace with “Zee”.
  • rTrim(string expression). This function takes a string expression and returns it without the trailing white space.

    • Example: rTrim("Hello "). This function trims the trailing white space and returns the word “Hello” without the space after it.
  • splitPart(string value, string delimiters, int findNum). This function searches a string value (first parameter) for a string delimiter (second parameter) and returns the word corresponding to the findNum value (third parameter). Note that the word before a delimiter corresponds to a findNum value “1” and the one after corresponds to a findNum value “2”. If the same delimiter is repeated after the second word, then the following word corresponds to a findNum value “3”. If the delimiter is different, then this delimiter is surrounded by a word corresponding to a findNum value “1” (before) and a findNum value “2” after. Sample string value: <FIRST_WORD>,<SECOND_WORD>, <THIRD_WORD>. If the findNum value is “2”, SECOND_WORD will be returned.

    • Example: splitPart("Apples-Oranges-Bananas","-",2). This function searches the string “Apples-Oranges-Bananas” for the delimiter ”-” and returns the second word occurrence, which is “Oranges” in this case.
    • Example: splitPart("Apples-Oranges,Bananas",",",2). This function returns “Bananas” as it is the word corresponding to the findNum value “2” for the ”,” delimiter.
  • substring(string exp, int exp2). Returns a substring from a string expression (the first parameter), starting with the character at a specified index by the second parameter. The first index starts at 0.

    • Example: substring("Hello", 2). This function returns the characters starting from index number 2, “llo”
  • substring(string exp, int exp, int exp). Returns a substring from a string expression (the first parameter), starting with the character at a specified index by the second parameter, and ending at a specified index by the third parameter). The first index starts at 0.

    • Example: substring("Hello", 1, 3). This function returns the characters starting from index number 1 to index number 3, “ell”.
  • trim(string exp). This function removes white space before and after a string expression.

    • Example: trim(" Hello there ") removes the white space before the word “Hello” and after the word “there”, returning “Hello there”.
  • upper(string expression). Returns the same string in upper-case letters.

    • Example: upper("Sally") returns the string “Sally” in upper case to read “SALLY”.

Formula Variables

The following table describes the date variables supported by Incorta Analytics. If you use date variables in formula columns of a schema, they are interpreted at load time and stored in memory. But if they are used in Business Schemas or a Dashboard, then they are calculated at query time.

  • currentDateDate. The current date of the Incorta Server time.
  • currentDayDate. Current day from the Incorta Server time.
  • currentMonth. Month of the current Incorta Server time.
  • currentYear. Year of the current Incorta Server time.
  • currentYearStartDate. First day of the current year: January, 1st, yyyy (time: 00:00:00.000).
  • currentMonthStartDate. First day of the current Month: January, 1st, yyyy (time: 00:00:00.000).
  • dayAgo. Date that corresponds to the current hour on a 24 hour clock.
  • weekAgo. Date exactly seven days before the current date (time: 00:00:00.000).
  • monthAgo. Date exactly one month before the current date (time: 00:00:00.000).
  • currentQuarter. Current quarter from the system time.
  • quarterAgo. Date exactly one quarter before the current date (time: 00:00:00.000).
  • yearAgo. Date exactly one year before the current date (time: 00:00:00.000).
  • lastYear. Year before the year of the current Unix Time.
  • lastYearStartDate. First day of the previous year: January, 1st, yyyy (time: 00:00:00.000).
  • lastMonthStartDate. First day of the previous month, as in: January, 1st, yyyy (time: 00:00:00.000).

© Incorta, Inc. All Rights Reserved.