> ## Documentation Index > Fetch the complete documentation index at: https://domoinc-bradley-turek-pfilter-operators-reference.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Beast Mode Functions Reference Guide ## Intro This reference guide describes supported functions in Beast Mode. *** ## Supported Functions You can use any of the following functions in calculations. You can see examples of many of the functions in the following articles: * [Sample Basic Transforms](/s/article/360043430073) * [Sample Card Element Transforms](/s/article/360043430093) * [Sample Date Transforms](/s/article/360042925494) * [Sample Mathematical Transforms](/s/article/360042925514) * [Sample Miscellaneous Transforms](/s/article/360043430113) * [Sample Period-over-Period Transforms](/s/article/360043430133) ### Aggregate Functions

Function Name

Description

Example

APPROXIMATE COUNT (DISTINCT)

{" "} Returns the approximate count of a number of unique values in a column.{" "}

APPROXIMATE\_COUNT\_DISTINCT(`Customers`)

AVG

Returns the average value for each series in a column.

AVG(`Operating Budget`)

CEILING

Returns the highest value for each series in a column.

{" "} This function differs from the MAX function in that{" "} CEILING is rounded whereas MAX is not rounded.{" "}

CEILING(`Operating Budget`)

COUNT

Returns the number of row values in a column.

COUNT(`Customers`)

COUNT (DISTINCT)

Returns the count of a number of unique values in a column.

COUNT(DISTINCT `Customers`)

FLOOR

Returns the lowest value for each series in a numeric column.

{" "} This function differs from the MIN function in that{" "} FLOOR is rounded whereas MIN is not.{" "}

FLOOR(`Operating Budget`)

HLL\_SKETCH\_EXTRACT

{" "} Extracts the approximate number of distinct values from an HLL++ sketch. Returns a numeric estimate. Syntax:{" "} HLL\_SKETCH\_EXTRACT(\)

HLL\_SKETCH\_EXTRACT(`sketch_col`)

HLL\_SKETCH\_INIT

{" "} Creates an HLL++ sketch from an expression or column. The optional{" "} max\_log\_k parameter controls sketch precision (range 4–21, default 12). Syntax:{" "} HLL\_SKETCH\_INIT(\ \[, max\_log\_k])

HLL\_SKETCH\_INIT(`Customers`)

HLL\_SKETCH\_MERGE

{" "} Returns the approximate distinct count from merged HLL++ sketches. Equivalent to calling{" "} {" "} HLL\_SKETCH\_EXTRACT(HLL\_SKETCH\_MERGE\_PARTIAL(\)){" "} . Syntax: HLL\_SKETCH\_MERGE(\)

HLL\_SKETCH\_MERGE(`sketch_col`)

HLL\_SKETCH\_MERGE\_PARTIAL

{" "} Combines multiple HLL++ sketches into a single aggregated sketch. Use in GROUP BY scenarios to consolidate sketches across rows or partitions. Syntax:{" "} HLL\_SKETCH\_MERGE\_PARTIAL(\)

HLL\_SKETCH\_MERGE\_PARTIAL(`sketch_col`)

MAX

Returns the highest value for each series in a numeric column.

{" "} This function differs from the CEILING function in that{" "} MAX is not rounded whereas CEILING is rounded.{" "}

MAX(`Operating Budget`)

MIN

Returns the lowest value for each series in a numeric column.

{" "} This function differs from the FLOOR function in that{" "} MIN is not rounded whereas FLOOR is rounded.{" "}

MIN(`Operating Budget`)

STDDEV\_POP

{" "} Returns the population standard deviation for each series in a numeric column.{" "}

STDDEV\_POP(`Values`)

SUM

Returns the sum of each series in a numeric column.

SUM(`Values`)

SUM (DISTINCT)

Returns the sum of unique values in a numeric column.

SUM(DISTINCT `Values`)

VAR\_POP

{" "} Returns the population standard variance for each series in a numeric column.{" "}

{" "} VAR\_POP(`Values`)

**Note:** The HLL++ sketch functions have platform-specific equivalents. If you are working with a Cloud Integrations DataSet, refer to the corresponding function in your connected platform:

Domo Function

BigQuery

Databricks

Snowflake

HLL\_SKETCH\_INIT

HLL\_COUNT.INIT

hll\_sketch\_agg

HLL\_ACCUMULATE

HLL\_SKETCH\_EXTRACT

HLL\_COUNT.EXTRACT

hll\_sketch\_estimate

HLL\_ESTIMATE

HLL\_SKETCH\_MERGE\_PARTIAL

HLL\_COUNT.MERGE\_PARTIAL

hll\_union\_agg

HLL\_COMBINE

HLL\_SKETCH\_MERGE

HLL\_COUNT.MERGE

hll\_union

HLL

Learn how to [use aggregate functions in Summary Numbers](#apply-a-calculation-to-a-summary-number). ### Mathematical Functions

Function Name

Description

Example

ABS

Returns the absolute value for all values in a numeric column.

{" "} In other words, any negative values become positive, and positive values stay the same. This is valuable when you want to see aggregation without considering positive and negative values.{" "}

ABS(`Operating Budget`)

MOD

{" "} Returns the remainder of each value in a numeric column divided by some specified number (dividend).{" "}

MOD(`Values`,2)

POWER

{" "} Returns each value in a numeric column raised to a given power. If the column contains multiple series, the values are summed for each series.{" "}

POWER(`Values`,2)

RAND

Returns random values between 0 and 1.

RAND(`Values`)

ROUND

{" "} Returns values in a numeric column rounded to the nearest specified decimal place.

{" "} If you do not include a decimal place value in the function, returned values are rounded to the nearest whole number.{" "}

ROUND(`Values`,1)

### Logical Functions

Function Name

Description

Example

CASE

{" "} Use to begin logical statements in which the value of data is replaced when certain criteria is reached (when...then or when...then, else). These statements use the following format:

CASE `Value` when x then 'resultx' else 'resulty' End

{" "} or

{" "} CASE when `Value`= x then 'resultx' else 'resulty' End{" "}

{" "} In other words, when the data in the "Value" column equals number{" "} x , value resultx is returned; otherwise value{" "} resulty is returned. (The value returned can be either a single-quoted string or a number.){" "}

{" "} For multiple conditional statements, use the following format:

{" "} CASE when `Value`= x then 1 when `Value`= y {" "} then 2 end{" "}

{" "} Although valid, avoid using the following inefficient format:

{" "} CASE when `Value`= x then 1 else CASE when `Value`={" "} y then 2 end end{" "}

IN Operator

In a CASE when clause, you can use the{" "} IN operator, which lets you specify multiple values to evaluate.

`column_name` IN (value1,value2,...)


CASE when `State` in ('NY', 'TX') then 0 else 1 end

LIKE Operator

In a CASE when clause, you can use the{" "} LIKE operator, which lets you search for a specified pattern in a column.

`column_name` LIKE pattern

{" "} CASE when `State` like '%TX%' or `State` like '%NY' then 0 else 1 end{" "}

You can use these wildcards with the LIKE operator:{" "}

  • % to match any number of characters, even zero characters.
    like '%TX%'

  • \_ to match exactly one character.
    like '\_hn%'

{" "} Values for the LIKE operator are matched in order of the WHEN statements. For example, if a value in the data matches the first and fourth LIKE operators, it is applied to the first operation.{" "}

CASE when `Amount`=1 then 10 else 0 End

CASE `Gender` when 'M' then 'Male' when 'F' then 'Female' end

CASE when `Pennies` >= 600 then 'Large' when `Pennies` \<= 300 then 'Small' else `Pennies` end

IFNULL

{" "} Used in logical statements in which you want to specify a replacement for null values.{" "}

IFNULL(`Values`,0)

NULLIF

{" "} Returns null if the value in the first column equals the value in the second column; otherwise returns the value in the first column.{" "}

NULLIF(`Value 1`,`Value 2`)

**Video - Case Statement Overview**