5

Formula functions tips.

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • General Rules

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

    Aggregation Functions

    • sum(double vector expression) Returns the sum of a vector expression.
    • max(double [] expression) Returns the maximum of a vector expression.
    • min(double [] expression) Returns the minimum of a vector expression.
    • median(double [] expression) Returns the median of a vector expression.
    • average(double [] expression) Returns the average of a vector expression.
    • count(object [] expression) Returns the count of a vector expression.
    • distinct(object [] expression) Returns the count of distinct values of a vector expression.

    Boolean Functions

    • 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".
      • 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 paramter) ends with a given string (second parameter), returning "true" if it is, otherwise it returns "false". It is not case-sensitive.
      • 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 paramter) ends with a given string pattern (second paramter) to return "true" if it is, otherwise it returns "false". It is not case-sensitive.
      • 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".
      • 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 lsit 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.
      • 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".
        • 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.
      • 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".
      • 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 paramter) starts with a given string (second paramter) to return "true" if it is, otherwise it returns "false". It is not case-sensitive.
      • startsWith(SALES.COUNTRIES. COUNTRY_NAME, 's') This function returns "true" if a country starts with the letter "s", otherwise it returns "false".

    Conditional Statements

    • case(bool exp1,result1,bool exp2,result2,..., else)
      • The "case" statement has the same functionality of the "if" statement.
      • 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

    • 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.
      • 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. int(2306.77) converts the double into an integer to equal 2307.
      • int(SALES.SALES. AMOUNT_SOLD) converts each record into an integer.
    • long(double exp)
      • Converts a double expression into a long.
      • 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.
      • 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/yy".
    • parseDouble(string exp, string mask)
      • Converts a string representation of a number into a double specified by a Java DecimalFormat mask. For more information about DecimalFormat patterns, please follow this link: https://docs.oracle.com/ javase/7/docs/api/java/text/ DecimalFormat.html
      • parseDouble("12,345", "#,##0.0#;(###0.#)") This function 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. Note 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.
      • parseTimestamp ("2001-12-24 23:05:33.000", "yyyyMM-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.
      • 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.
      • timestamp ("2001-12- 24 23:55:12.000") This function converts the string representation of the timestamp into a timestamp field.

    Filter Functions

    • 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).
      • 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).
      • 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

    • descendantOf(field, $sessionVariable)
      • This function takes in a field (column of a hierarchical table) as the first parameter and returns "true" for each row where the first parameter is a descendant of the second one
      • descendantOf(HR. EMPLOYEES.FIRST_ NAME, "John") This function returns "true" for each record that is descendant of an employee with the first name "John", i.e. whose manager is "John".
      • descendantOf(HR. EMPLOYEES.FIRST_ NAME, $Emp) If you do not want to hard-code the employee first name, you can create a presentation variable with a default name that can be changed by the user 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.
      • 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 (sting 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.
      • schemaRefreshTime ('SALES') This function returns the last refresh start time for the "SALES" schema.

    Scalar Arithmetic Functions

    • 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.
      • mod (19/3) = 1, [since 19/3 = 6 + 1/3].
      • mod (20/3) = 2, [since 20/3 = 6 + 2/3].
      • mod (21/3) = 0, [since 21/3 = 7 + 0/3].
    • 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

    Scalar String Functions

    Reply Like
Like5 Follow
  • Status Answered
  • 5 Likes
  • 1 yr agoLast active
  • 1Replies
  • 1861Views
  • 2 Following