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:

    1. $ - Root node of json object

    2. @ - Filter predicate

    3. * - Wildcard

    4. .. - It can be understood as recursive search

    5. .< name > - Represent a child node

    6. [‘< name >’ (, ‘< name >’)] - Represent one or more child nodes

    7. [‘< number >’ (, ‘< number >’)] - Represent one or more array subscripts

    8. [ start:end ] - Array fragment, interval [start, end], excluding end

    9. [ ?(< 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)

Updated: