Chapter 12 Formula List
The following formula lists are mainly used in formula addition, formula search in the search analysis page and formula editing in the system management.
In the example, the contents in the formula brackets are Attribute column names, Measure column names, numbers, strings, etc., and quotation marks should be added for the input of strings.
12.1 Aggregation
Table 12-1 Aggregation formula
| Formula | Description | Case |
|---|---|---|
| average | Average value of a Measure column | Average (Quantity) |
| average_if | Average value by condition | average_if (Unit_Price>8, Quantity) |
| max | Maximum value of a Measure column | max (Quantity) |
| max_if | Maximum value by condition | max_if (Unit_Price>8, Quantity) |
| min | Minimum value of a Measure column | min (Quantity) |
| min_if | Minimum value by condition | min_if (Unit_Price>8, Quantity) |
| sum | Summation of a Measure column | sum (Quantity) |
| sum_if | Sum by condition | sum_if (Unit_Price>8, Quantity) |
| count | Returns the number of rows for the specified column | Count (Product_Name) |
| count_if | Count by condition | count_if (Unit_Price>8, Quantity) |
| unique_count | Distinct count of the specified column | unique count (Product_Name) |
| unique_count_if | Distinct count by condition | unique_count_if (Unit_Price>8, Quantity) |
| stddev | Standard deviation of a Measure column | stddev (Quantity) |
| stddev_if | Standard deviation by condition | stddev_if (Unit_Price>8, Quantity) |
| variance | Variance of a Measure column | Variance (Sales) |
| variance_if | Variance by condition | variance_if (Unit_Price>8, Quantity) |
12.2 Analyze
-
For “cumulative_” and “group_” formulas, it takes a Measure column and one or more Attribute column(s).
-
For “moving_” formulas, it takes a Measure column, one or more Attribute column(s), and two integers to define the aggregation range. The first number is the number of lines moved up from the current position, and the second number is the number of lines moved down.
Table 12-2 Analyze formula
| Formula | Description | Case |
|---|---|---|
| cumulative_average | Return the cumulative average value of each row sorted by the specified Attribute | cumulative_average (Quantity, Order_Date, Category)Note: The cumulative average of Quantity first sorted by Order_Date and then by Category. |
| cumulative_max | Return the cumulative maximum value of each row sorted by the specified Attribute | cumulative_max (Quantity, Order_Date, Category) |
| cumulative_min | Return the cumulative minimum value of each row sorted by the specified Attribute | cumulative_min (Quantity, Order_Date, Category) |
| cumulative_sum | Return the cumulative sum of each row sorted by the specified Attribute | cumulative_sum (Quantity, Order_Date, Category) |
| cumulative_count | Return the cumulative number of rows for each row sorted by the specified Attribute | cumulative_count (Quantity, Order_Date, Category) |
| cumulative_stddev | Return the cumulative standard deviation of each row sorted by the specified Attribute | cumulative_stddev (Quantity, Order_Date, Category) |
| cumulative_variance | Return the cumulative variance of each row sorted by the specified Attribute | cumulative_variance (Quantity, Order_Date, Category) |
| group_average | Return the average value of the measure grouped by the Attribute(s) | group_average (Quantity, Order_Date, Category)Note: Average Quantity of each Category in each Order_Date. |
| group_max | Return the maximum value of the measure grouped by the Attribute(s) | group_max (Quantity, Order_Date, Category) |
| group_min | Return the minimum value of the measure grouped by the Attribute(s) | group_min (Quantity, Order_Date, Category) |
| group_sum | Return the sum of the measure grouped by the Attribute(s) | group_sum (Quantity, Order_Date, Category) |
| group_count | Return the number of rows of the measure grouped by the Attribute(s) | group_count (Quantity, Order_Date, Category) |
| group_stddev | Return the standard deviation of the measure grouped by the Attribute(s) | group_stddev (Quantity, Order_Date, Category) |
| group_variance | Return the variance of the measure grouped by the Attribute(s) | group_variance (Quantity, Order_Date, Category) |
| moving_average | Return the average value of the measure over the given range | moving_average (Unit_Cost, 7, 6, Order_Date) |
| moving_max | Return the maximum value of the measure over the given range | moving_max (Unit_Cost, 7, 6, Order_Date) |
| moving_min | Return the minimum value of the measure over the given range | moving_min (Unit_Cost, 7, 6, Order_Date) |
| moving_sum | Return the sum of the measure over the given range | moving_sum (Unit_Cost, 7, 6, Order_Date) |
| moving_count | Return the number of rows of the measure over the given range | moving_count (Unit_Cost, 7, 6, Order_Date) |
| moving_stddev | Return the standard deviation of the measure over the given range | moving_stddev (Unit_Cost, 7, 6, Order_Date) |
| moving_variance | Return the variance of the measure over the given range | moving_variance (Unit_Cost, 7, 6, Order_Date) |
12.3 Conversion
Table 12-3 Conversion formula
| Formula | Description | Case |
|---|---|---|
| to_bool | Convert the corresponding number to a boolean type | to_bool (9.37) |
| to_date | Convert the specified numeric and string type date values to date format (Only support string conversion in ‘%Y-%m-%d %H:%M:%S’ format) | to_date (10.07) |
| to_double | Convert the corresponding number to a double type | to_double (Order_ID) |
| to_integer | Convert the corresponding number to an integer type | to_integer (Order_ID) |
| to_string | Convert the corresponding number to a string type | to_string (Order_Date) |
12.4 Date
- Week starts at every Monday, i.e., the first day of a week is Monday.
Table 12-4 Date formula
| Formula | Description | Case |
|---|---|---|
| diff_days | Calculate the difference in days from the first date minus the second date, if the difference exceeds 12 hours, it will be counted as one day | diff_days (Ship_Date, Order_Date) |
| diff_time | Calculate the difference in seconds from the first date minus the second date | diff_time (Ship_Date, Order_Date) |
| add_years | Add years to Date column | add_years (Order_Date, 8) |
| add_months | Add months to Date column | add_months (Order_Date, 8) |
| add_weeks | Add weeks to Date column | add_weeks (Order_Date, 8) |
| add_days | Add days to Date column | add_days (Order_Date, 8) |
| add_hours | Add hours to Date column | add_hours (Order_Date, 8) |
| add_minutes | Add minutes to Date column | add_minutes (Order_Date, 8) |
| add_seconds | Add years to date column | add_seconds (Order_Date, 8) |
| month_number | Return the month number (1-12) in the year for the given time | month_number (Order_Date) |
| month_number_of_quarter | Return the month number (1-3) in the quarter for the given time | month_number_of_quarter (Order_Date) |
| week_number_of_year | Return the week number (1~53) in the year for the given time | week_number_of_year (Order_Date) |
| week_number_of_quarter | Return the week number (1~15) in the quarter for the given time | week_number_of_quarter (Order_Date) |
| week_number_of_month | Return the week number (1~6) in the month for the given time | week_number_of_week (Order_Date) |
| day | Return the day number (1~31) in the month for the given time | day (Order_Date) |
| day_number_of_year | Return the day number (1~366) in the year for the given time | day_number_of_year (Order_Date) |
| day_number_of_quarter | Return the day number (1~91) in the quarter for the given time | day_number_of_quarter (Order_Date) |
| day_number_of_week | Return the day number (1~7) in the week for the given time | day_number_of_week (Order_Date) |
| hour_of_day | Return the hour number (0~23) in the day for the given time | hour_of_day (Order_Date) |
| day_of_week | Returns the day (Monday ~ Sunday) of the week for the given time | day_of_week (Order_Date) |
| is_weekend | Return true if the given date is a Saturday or Sunday | is_weekend (Order_Date) |
| start_of_year | Return the start date of the year to which the given time belong | start_of_year (Order_Date) |
| start_of_quarter | Return the start date of the quarter to which the given time belong | start_of_quarter (Order_Date) |
| start_of_month | Return the start date of the month to which the given time belong | start_of_month (Order_Date) |
| start_of_week | Return the start date of the week to which the given time belong | start_of_week (Order_Date) |
| start_of_day | Return the start date of the day to which the given time belong | start_of_day (Order_Date) |
| date | Return the date part of the given time | date (Order_Date) |
| year | Return the year part of the given time | year (Order_Date) |
| month | Return the month part of the given time (January~December) | month (Order_Date) |
| time | Return the time part of the given date/time | time (Order_Date) |
| now | Return the current time | now( ) |
12.5 Relation
Table 12-5 Relation formula
| Formula | Description | Case |
|---|---|---|
| != | Return true if the two values are not equal, otherwise returns false | Quantity != 10000 |
| < | Return true if the first value is less than the second value else return false | Quantity < 10000 |
| <= | Return true if the first value is less than or equal to the second value else return false | Quantity <= 10000 |
| = | Return true if the two values are equal, otherwise return false | Quantity = 10000 |
| > | Return true if the first value is larger than the second value else return false | Quantity > 10000 |
| >= | Return true if the first value is larger than or equal to the second value else return false | Quantity >= 10000 |
| greatest | Return the larger value | greatest (Cost, Sales) |
| least | Return the smaller value | least (Cost, Sales) |
12.6 Number
Table 12-6 Number formula
| Formula | Description | Case and Result |
|---|---|---|
| * | Return the product | 2*6 |
| + | Return the sum | 1+5 |
| - | Return the difference | 8-2 |
| / | Return the quotient | 12/2 |
| ^ | Return the power | 5^2 = 25 |
| abs | Return the absolute value | abs(-5) = 5 |
| acos | Return the arc cosine | acos(0.5) |
| asin | Return the arc sine | asin(0.5) |
| atan | Return the arc tangent | atan(1) |
| atan2 | Return the arc tangent | atan2(1,2) |
| cbrt | Return the cubic root | cbrt(27) = 3 |
| ceil | Return the smallest integer that is larger than or equal to the current value | ceil(5.9) = 6 |
| cos | Return the cosine | cos(0.5) |
| cube | Return the cube value | cube(3) = 27 |
| exp | Return the power of natural constant e | exp(2) |
| exp2 | Return the power of 2 | exp2(3) = 8 |
| floor | Return the largest integer that is larger than or equal to the current value | floor(3.1) = 3 |
| ln | Return the natural logarithm | ln(3) |
| log10 | Return the logarithm base on 10 | log10(3) |
| log2 | Return the logarithm base on 2 | log2(3) |
| mod | Return the remainder of first number divided by the second number | mod(8,3) = 2 |
| pow | Return the power value | pow(2,3) = 8 |
| random | Return a random number between 0 and 1 | random() |
| round | Round the first number to n (second number, default 1) decimal places | round(36.355, 2) = 36.36 |
| sign | If the number > 0, return 1; If the number < 0, return -1; If the number = 0, return 0 |
sign(-50) |
| sin | Return the sine | sin(0.5) |
| sq | Return the square value | sq(8) |
| sqrt | Return the square root | sqrt(64) |
| tan | Return the tangent | tan(0.5) |
| safe_divide | Safe division to prevent the problem of dividing by 0 and reporting an error | safe_divide (Sales, Quantity) |
12.7 Logic
Table 12-7 Logic formula
| Formula | Description | Case |
|---|---|---|
| if..then..else | Conditional operator | if Price >= 500 then “High” else if Price < 500 and Price >= 200 then “Middle” else “Low” |
| and | Return true when both conditions are true, otherwise return false | X=1 and X>3 |
| not | Return true if the condition is false, otherwise return false | not (2>3) = true |
| or | Return true when either condition is true, otherwise return false | X=1 or X>3 |
| ifnull | Return the first value if it is not null, otherwise return the second value | ifnull (cost, ‘unknown’) |
| isnull | Return true if the value is null | isnull (null) = true |
12.8 String
- In the formula, the value in the column needs to be enclosed in quotation marks, and the column name in the source table is not enclosed in quotation marks.
Table 12-8 String formula
| Formula | Description | Case and Result |
|---|---|---|
| concat | Return the concatenated string | concat (“test”, “123”) = “test123” concat (“Category: ”, Category) |
| contains | Return true if the first string contains the second string, otherwise return false | contains (“broomstick”, “room”) = true contains (Product_Name, “flexible”, “durable”, “phone”) |
| not_contains | Return true if the first string not contains the second string, otherwise returns false | not_contains (“broomstick”, “room”) = false not_contains (Product_Name, “flexible”, “durable”, “phone”) |
| strlen | Return the length of string | strlen (“smith”) = 5 |
| strpos | Return the numeric position (starting from 0) of the first occurrence of the second string in the first string, or -1 if not found | strpos (“haystack_with_needles”, “needle”) = 14 |
| substr | Return substring, starting from the first number (begin at 1) with a length of the second number | substr (“persnickety”, 4, 7) = snicket |
| strupper | Convert the input string to uppercase | strupper (“example”) = EXAMPLE |
| strlower | Convert the input string to lowercase | strlower (“EXAMPLE”) = example |
| begins_with | Return true if the first string begin with the second string, otherwise returns false | begins_with (“example”,”exa”) = true begins_with (Product_Name, “flexible”, “durable”) |
| not_begins_with | Return true if the first string does not begin with the second string, otherwise return false | not_begins_with (“example”,”exa”) = false not_begins_with (Product_Name, “flexible”, “durable”) |
| ends_with | Return true if the first string end with the second string, otherwise returns false | ends_with (“example”,”ple”) = true ends_with (Product_Name, “envelope”, “phone”) |
| not_ends_with | Return true if the first string does not end with the second string, otherwise return false | not_ends_with (“example”,”ple”) = false not_ends_with (Product_Name, “envelope”, “phone”) |
12.9 JSON
-
json_path rules：
-
$ - Root node of json object
-
@ - Filter predicate
-
* - Wildcard
-
.. - It can be understood as recursive search
-
.< name > - Represent a child node
-
[‘< name >’ (, ‘< name >’)] - Represent one or more child nodes
-
[‘< number >’ (, ‘< number >’)] - Represent one or more array subscripts
-
[ start:end ] - Array fragment, interval [start, end], excluding end
-
[ ?(< expression >)] - Filter expression. The result of the expression must be Boolean
-
Table 12-9 JSON formula
| Formula | Description | Case and Result |
|---|---|---|
| json_format | Convert JSON objects to JSON strings | json_format (＂hello＂) json_format (Product_Name) |
| json_parse | Convert a string (JSON format) into a JSON object | json_parse (＂focus＂) json_parse (Segment) |
| json_extract | Extract the JSON object under the specified path from the JSON object (the return value is still JSON type) | json_extract (json, json_path) |
| json_extract_scalar | Extract the scalar JSON object under the specified path from the JSON object and return it as a string. If the extraction path is not a scalar JSON object, return null | json_extract_scalar (json, json_path) |
| json_size | Return the length of the JSON object under the specified path of the JSON object. If there is a JSON object or JSON array under the specified path, the length is the number of members of the object or array. If it is a scalar object, it returns 0 | json_size (json, json_path) |