How do I work with functions in PowerMetrics? The Function Reference Guide

Use ARRAY to join together single values and return them as a single list of data in the order provided.

Syntax and Parameters: ARRAY ( data )

data: The values to join together including strings or columns of data from different data sources.

When to use this function: To combine data from multiple sources, such as data sources and results references.

Example:

Append column B to column A.

A:A contains [A, B, C, D]

B:B contains [E, F, G]

Formula:

ARRAY(@A:A, @B:B)

Results:

A, B, C, D, E, F, G

Use BLANK to return one blank value or a specified number of blank values.

Syntax and Parameters: BLANK ( [count] )

count: [optional] The number of blank items to be returned. Negative numbers are treated as positives. The maximum number for the count parameter is 1000000.

When to use this function: To replace a non-blank cell with a blank cell.

Example:

Return a list of 5 blank items.

Formula:

BLANK(5)

Results:

5 blank items

Use COUNTDISTINCT to count the number of unique items in each group. The results are returned in alphabetical order and (optionally) aligned with the results of the GROUP function.

Syntax and Parameters: COUNTDISTINCT ( values )

values: A list of 1 or more values.

Related functions:

• GROUP
• GROUPBY

When to use this function: To aggregate data according to specified groups.

Example:

Count the number of times each State appears in the data. A:A contains [California, Texas, California, California, New York, New York]

A:A contains [California, Texas, California, California, New York, New York]

Formula:

COUNTDISTINCT(@A:A)

Results:

3,2,1

Use FIRST to return the first values in a list of data where the number of values is specified by the count parameter.

Syntax and Parameters: FIRST( values , [count] )

values: A list of 1 or more items.

count: [optional] The number of items to return from the data.

Example:

A:A contains [California, Texas, California, California, New York, New York]

Formula:

FIRST(@A:A)

Results:

California

Note: If the value of the count parameter is greater than the number of items in the column, all items will be returned. If the data is grouped and there are fewer unique values than the value of the count parameter, only the number of unique values will be returned.

Use GROUP to group data into unique instances and hide duplicate values. The results are returned in alphabetical order.

Syntax and Parameters: GROUP ( values )

values: A list of 1 or more values

Related functions:

• COUNTDISTINCT
• GROUPBY

Example:

Get the list of states from column A.

A:A contains [California, Texas, California, California, New York, New York]

Formula:

GROUP(@A:A)

Results:

California, New York, Texas

Use GROUPBY to return values based on a specified aggregation method so that the unique values align with a parallel column.

Syntax and Parameters: GROUPBY (values, measure, [method])

values: A list of 1 or more values.

measure: A list of 1 or more values. Values and measure must have the same number of items.

method: [optional] The aggregation method to use when grouping. The default method is SUM.

Related functions:

• COUNTDISTINCT
• GROUP

Example:

Sum the data in column B by state.

A:A contains [California, Texas, California, California, New York, New York]

B:B contains [3, 7, 20, 10, 5, 11]

Formula:

GROUPBY(@A:A, @B:B)

Results:

33, 16, 7

Example:

Average the data in column B by state.

A:A contains [California, Texas, California, California, New York, New York]

B:B contains [3, 7, 20, 10, 5, 11]

Formula:

GROUPBY(@A:A, @B:B, average)

Results:

11, 8, 7

Use LAST to return the last values in a list of data where the number of values is specified by the count parameter.

Syntax and Parameters: LAST( values , [count] )

values: A list of 1 or more values.

count: [optional] The number of items to return from the data.

Example:

A:A contains [California, Texas, California, California, New York, New York]

Formula:

LAST(@A:A)

Results:

New York

Note: If the value of the count parameter is greater than the number of items in the column, all items will be returned. If the data is grouped and there are fewer unique values than the value of the count parameter, only the number of unique values will be returned.

Use LOOKUP to correlate data between two data sources. For each input item, search for the first match in keys and return the value at the corresponding position in results. If there is no match, a blank is returned.

Syntax and Parameters: LOOKUP(input, keys, results)

input: A list of 1 or more items.

keys: A list of 1 or more items that intersects with the input list.

results: A list of 1 or more items from the same source as keys. Must contain the same number of items as keys.

When to use this function: To correlate data between data sources and to align data across sub-components (for example, in a Bar/Line chart to align a series with the X-axis).

Example:

Get the countries from data source 2 for the cities listed in data source 1. Use the city name in each data source to lookup the data.

Data Source 1 A:A contains [Vancouver, Ottawa, London, Washington, New York, Montreal],

Data Source 2 A:A contains [New York, Washington, Montreal, Ottawa, Vancouver],

Data Source 2 B:B contains [US, US, CAN, CAN, CAN]

Formula:

LOOKUP(@A:A, @A:A, @B:B)

Results:

CAN, CAN, BLANK(), US, US, CAN

Use PADVALUES to return selected values with padding or additional values at the end of the data.

Syntax and Parameters: PADVALUES ( values, times, [with value] )

values: A list of 1 or more items.

times: The number of values to add to the data in the values parameter. The maximum number is 1000000.

with value: [optional] The value to add. If not specified, values are padded with blanks.

When to use this function: To make two lists of data the same size and to ensure data alignment.

Example:

Pad column A with 5 zeros.

A:A contains [3, 5, 20]

Formula:

PADVALUES( @A:A, 5, 0)

Results:

3, 5, 20, 0, 0, 0, 0, 0

Example:

In this example the difference between the number of values in column B and column C is used as the amount of values to pad column C with.

Formula:

PADVALUES (@C:C, COUNTALL(@B:B)-COUNTALL(@C:C))

Use REPEAT to replicate one or more values and return a list containing the number of copies specified.

Syntax and Parameters: REPEAT (values, times)

values: A list of 1 or more items.

times: The number of times to repeat the values. If less than or equal to 0, 0 items are returned. The maximum number of times is 1000000.

Example:

REPEAT("Canada", 3)

The result of this formula is Canada, Canada, Canada

Use REPLACE to replace items that match a specified value with another value.

Syntax and Parameters: REPLACE( values, matching, [with value] )

values: A list of 1 or more values.

matching: A list of 1 or more values to be replaced.

with value: [optional]

Example:

Change CA to California.

A:A contains [CA, New York, CA, CA, Texas]

Formula:

REPLACE(@A:A, "CA", "California")

Results:

California, New York, California, California, Texas

Use REVERSE to reverse the order of a list of values.

Syntax and Parameters: REVERSE( values )

values: A list of 1 or more values.

Example:

A:A contains [1, 2, 3, 4, 5]

Formula:

REVERSE(@A:A)

Results:

5, 4, 3, 2, 1

Use SELECT to select values from a list according to specified criteria.

Syntax and Parameters: SELECT ( data, condition )

data: A list of 1 or more items.

condition: A list of true and false values, typically a formula which combines 1 or more Logic functions. Data and condition must have the same number of items.

Example:

Return the cities in California.

A:A contains [California, New York, California, California, Texas]

B:B contains [Los Angeles, New York, San Francisco, Palo Alto, Houston]

Formula:

SELECT(@B:B, @A:A="California")

Results:

Los Angeles, San Francisco, Palo Alto

Use the SLICE function to return the subset of values between the start and end positions. If start and end parameters are not specified the first row is removed.

Syntax and Parameters: SLICE ( values, [start] , [end])

values: A list of 1 or more items.

start: [optional] Indicates the number of items sliced off the top.

end: [optional] Indicates the position of the last item to be returned.

When to use this function: Often used to remove row headers from data.

Example:

Remove the header cell from a column.

A:A contains [A, B, C, D, E]

Formula:

SLICE(@A:A)

Results:

B, C, D, E

Note: If start and end parameters contain negative values, values are returned from the end of the list.

Use the SORT function to sort values according to the specified order.

Syntax and Parameters: SORT ( values, type, [return values])

values: A list of 1 or more values.

type: The sort order. Choose between: ascending, ascending numeric, descending, or descending numeric.

return values: [optional] A list of 1 or more values that corresponds to the values parameter.

Example:

Sort column A in descending order.

A:A contains [Ontario, Alberta, Quebec]

Formula:

SORT(@A:A, descending)

Results:

Quebec, Ontario, Alberta

Use the SPLICE function to return the values in the values parameter but delete the number of values specified in the count parameter from the position specified in the index parameter. Optionally inserts the value in the insert parameter at index.

Syntax and Parameters: SPLICE ( values, index, count,[insert])

values: A list of 2 or more values.

index: Position in the array to begin the splice.

count: Number of values to delete.

insert: [optional] Value to insert.

Example:

Splice the value "x" into column A.

A:A contains [a, b, c, d, e]

Formula:

SPLICE(@A:A, 2, 2, "x")

Results:

a, b, x, e

Use the TRIM function to remove the values specified in the pattern parameter or to remove all blank values if no pattern is provided.

Syntax and Parameters: TRIM ( values, [pattern] )

values: A list of 1 or more values.

pattern: [optional] 1 or more items to be removed.

Note: The pattern parameter is case sensitive.

Example:

Remove blanks from the data.

A:A contains [ABC, BLANK(), FGH]

Formula:

TRIM(@A:A)

Results:

ABC, FGH

Use AND to return the results of conditions. Returns true if a condition is true, otherwise returns false. The AND function tests each value in the condition.

Syntax and Parameters: AND ( condition )

condition: 1 or more true/false values, typically the result of a comparison or formula.

Example:

Return true if Column A contains Mexico and Column B contains high.

A:A contains [Canada, United States, Mexico, China, Africa],

B:B contains [high, low, high, low, low]

Formula:

AND((@A:A="Mexico"), (@B:B="high"))

Results:

false, false, true, false, false

Use BETWEEN to return true or false if a value is numerically between a (inclusive) start and end.

Syntax and Parameters: BETWEEN ( values, start, end )

values: A list of 1 or more numeric items.

start: Numeric start of range.

end: Numeric end of range.

When to use this function: To determine if a list of dates falls within a specific date range (all dates must be in Unix time format).

Example:

Returns true if today is between 1735707600(2025/01/01) and 1767157200(2025/12/31).

Formula:

BETWEEN(TODAY( ), 1735707600, 1767157200)

Results:

false

Note: The DATE function is used to convert the dates to Unix Time format, enabling DATE_STARTOF, TODAY, and DATE_ENDOF to accurately recognize the dates.

Use IF to test a condition and specify the result of the condition if it evaluates to true or false.

Syntax and Parameters: IF ( condition , if true , if false )

condition: A list of 1 or more values to test.

if true: Data returned if the condition is true.

if false: Data returned if the condition is false.

Example:

A:A contains [Canada, United States, Mexico, China, Africa]

B:B contains [high, low, high, low, low]

C:C contains [80, 50, 90, 40, 30]

Formula:

IF(@A:A= "Canada", @C:C, 0)

Results:

80, 0, 0, 0, 0

Note: It is strongly recommended to only nest up to two IF formulas. If more conditions are needed, use SWITCH or SELECT instead.

Use IN to return true or false to determine whether the values are present in the set parameter.

Syntax and Parameters: IN ( values, set )

values: A list of 1 or more values.

set: A list of 1 or more values.

Example:

A:A contains [Canada, United States, Mexico, China, Brazil]

Formula:

IN(@A:A, ARRAY("Canada", "Mexico", "United States"))

Results:

true, true, true, false, false

Use NOT to reverse the logical value of each item in values (false returns as true and true returns as false).

Syntax and Parameters: NOT ( values )

values: A list of 1 or more values that evaluates to true or false.

Example:

A:A contains [Canada, United States, Mexico, China, Brazil]/p

Formula:

NOT(@A:A="Canada")

Results:

false, true, true, true, true

For each condition, returns true if at least one of the conditions is true and returns false if none of the conditions are true.

Syntax and Parameters: OR (condition )

condition: A list of 1 or more true/false values, typically the result of a comparison or formula.

When to use this function: commonly used with the SELECT function when at least one of multiple conditions must be met.

Example:

Return true if either Column A contains Mexico or Column B contains high.

A:A contains [Canada, United States, Mexico, China, Africa],

B:B contains [high, low, high, low, low]

Formula:

OR(@A:A="Mexico", @B:B="high")

Results:

true, false, true, false, false

Use SWITCH to switch a value to another value based on whether the case is evaluated to be true. If no match is found null is returned.

Syntax and Parameters: SWITCH ( data, case, values,[case, value],[“_default_”,value])

data: A list of 1 or more values.

case: The condition to be evaluated as either true or false.

values: The value to be returned if the case is true.

Note: case and value can be repeated as many times as necessary to cover all conditions.

Example:

A:A contains [Canada, United States, Mexico, China, Brazil]

Formula:

SWITCH(@A:A, "Canada", "1", "United States", "2", "Netherlands", "3", "_default_", "0")

Results:

1, 2, 0, 0, 0

Use the ABS function to calculate the absolute (positive) value of all numeric values in a range of data. Returns 0 for non-numeric values and blank values.

Syntax and Parameters:

ABS (data )

data: A list of 1 or more values

Example:

A:A contains [0, -1, BLANK(), 2, -3, 4, "abc"]

Formula:

ABS(@A:A)

Results:

0, 1, 0, 2, 3, 4, 0

Use the AVERAGE function to return the average of all numeric items. Non-numeric and blank items are ignored. This function returns a single value.

Syntax and Parameters:

AVERAGE (data )

data: A list of 1 or more values to find the average of.

Example:

Returns the average of column A.

A:A contains [0, -1, BLANK(), 2, -3, 4, abc]

Formula:

AVERAGE(@A:A)

Results:

0.4

Use the AVERAGEIF function to return the average of all numeric items in the average range parameter corresponding to a true value in a condition. This function returns a single value.

Syntax and Parameters: AVERAGEIF (condition, average range )

condition: A list of 1 or more true/false values.

average range: A list of 1 or more numeric items. Non-numeric values return as 0.

Examples:

Formula:

AVERAGEIF(ARRAY("true", "false", "true"), ARRAY(1, 2, 3))

Results:

2

Formula:

AVERAGEIF(ARRAY("false", "true"), ARRAY(1, 2, 3))

Results:

2

Note: If condition has fewer items than average range, the remaining condition values default to false.

Use CEILING to round numbers up to the nearest multiple of significance. Non-numeric values return as 0.

Syntax and Parameters: CEILING ( data, significance )

data: A list of 1 or more values to round up.

significance: [optional] The multiple to round up to.

Example:

Round up the values in column A.

A:A contains [0.22, -10.24, 12.35, -3.8, 4.5, 7, abc]

Formula:

CEILING(@A:A)

Results:

1, -10, 13, -3, 5, 7, 0

Note: CEILING(data, significance) = FLOOR(data, -significance).

Use COUNT to return a count of all non-blank (numeric and text) items in data.

Syntax and Parameters: COUNT ( data ) This function returns a single value.

data: A list of 1 or more items.

Example:

Count the number of non-blank items in column A.

A:A contains [0, -1, BLANK(), 12, 7, abc].

Formula:

COUNT(@A:A)

Results:

5

Use COUNTALL to count all numbers, text, and blank values and return the count as a single value.

Syntax and Parameters: COUNTALL ( data )

data: A list of 1 or more items.

Example:

Count the number of items in column A.

A:A contains [0, -1, BLANK(), 12, 7, abc]

Formula:

COUNTALL(@A:A)

Results:

6

Use COUNTBLANK to count all blank values and return the count as a single value.

Syntax and Parameters: COUNTBLANK ( data )

data: A list of 1 or more items.

Example:

Count the number of blank items in column A.

A:A contains [0, -1, BLANK(), 12, 7, abc]

Formula:

COUNTBLANK(@A:A)

Results:

1

Use COUNTIF to test each value in a condition and count the true results of the condition.

Syntax and Parameters: COUNTIF ( condition ) This function returns a single value.

condition : A list of true and false values.

Example:

Count the number of items in column A that equal 7.

A:A contains [0, -1, BLANK(), 12, 7, abc]

Formula:

COUNTIF(@A:A = 7)

Results:

1

Use COUNTNUMERIC to count all the numeric values in a list of data. This function returns a single value.

Syntax and Parameters: COUNTNUMERIC ( data )

data: A list of 1 or more items.

Example:

Count the number of numeric items in column A.

A:A contains [0, -1, BLANK(), 12, 7, abc]

Formula:

COUNTNUMERIC(@A:A)

Results:

4

Use CUMULATIVE to calculate the cumulative sums in a list of numeric data.

Syntax and Parameters: CUMULATIVE ( data )

data: A list of 1 or more numeric items.

Example:

A:A contains [5, -1, 2, -3, 4]

Formula:

CUMULATIVE(@A:A)

Results:

5, 4, 6, 3, 7

Use CUMULATIVE_DIFFERENCE to calculate the difference between the current and previous values from a list of values.

Syntax and Parameters: CUMULATIVE_DIFFERENCE ( data, [first value] )

data: A list of 1 or more numeric items.

first value: [optional] Specify the way the first value should be treated by choosing one of the options below:

• Ignore first value (default): Ignores the first value and returns the first cumulative difference as the first value.
• Use first value: Returns the first value in the data and then returns the cumulative difference from the first value.
• Use zero: Returns zero as the first value.

Example:

A:A contains [5, -1, 2, -3, 4]

Formula:

CUMULATIVE_DIFFERENCE(@A:A

Results:

-6, 3, -5, 7

Use FLOOR to round numbers down to the nearest multiple of significance. Non-numeric values return as 0.

Syntax and Parameters: FLOOR ( data )

data: A list of 1 or more values to round down.

significance: [optional] The multiple to round down to.

Example:

Round down the values in column A.

A:A contains [0.22, -10.24, 12.35, -3.8, 4.5, 7, abc]

Formula:

FLOOR(@A:A)

Results:

0, -11, 12, -4, 4, 7, 0

Note: FLOOR( data, significance) = CEILING( data, -significance).

Use MAX to return the maximum value from a list of numeric values. This function returns a single value.

Syntax and Parameters: MAX ( value )

value: A list of 1 or more numeric values.

Example:

A:A contains [15, -150, 15.5]

Formula:

MAX(@A:A)

Result:

15.5

Use MEDIAN to return the middle value from a list of numeric values. This function returns a single value.

Syntax and Parameters: MEDIAN ( value )

value: A list of 1 or more numeric values.

Example:

A:A contains [1, 20, 3]

Formula:

MEDIAN(@A:A)

Result:

3

Use MIN to return the minimum value from a list of numeric values. This function returns a single value.

Syntax and Parameters: MIN ( value )

value: A list of 1 or more numeric values.

Example:

A:A contains [-15, 150, -15.5]

Formula:

MIN(@A:A)

Result:

-15.5

Use MOD to return the remainder of each value after dividing each value by the modulus value.

Syntax and Parameters: MOD ( values, modulus )

values: A list of 1 or more numeric values.

modulus: Divisor

Example:

A:A contains [0.22, -10.24, 7.35, -3.8, 4.5]

Formula:

MOD(@A:A, 2)

Results:

0.22, -0.24, 1.35, -1.8, 0.5

Use MODE to return the most frequently occurring lowest value found in a list of items. This function returns a single value.

Syntax and Parameters: MODE ( value )

value: A list of 1 or more numeric values.

Example:

A:A contains [0.22, -10.24, 7.35, -3.8, 4.5, -10.24]

Formula:

MODE(@A:A)

Result:

-10.24

Use POWER to raise each base to the specified exponent.

Syntax and Parameters: POWER ( bases, exponent )

bases: A list of 1 or more numeric items.

exponent: A single numeric value.

Example:

Raise the values in column A to the power of 3.

A:A contains [4, 1.7, -8]

Formula:

POWER(@A:A, 3)

Results:

64, 4.9, -512

Use ROUND to return a number that is rounded to a specified number of digits or to a whole number if no digits are provided.

Syntax and Parameters: ROUND ( data, [digits] )

data: The number to round.

digits: [optional] The number of digits to round the number to.

Example:

A:A contains [4.33, 1.5, -8.454, 3.567, 20]

Formula:

ROUND(@A:A, 1)

Results:

4.3, 1.5, -8.5, 3.6, 20

Use SUM to return the sum of all non-blank values in a range of data. SUM calculates based on how the data parameter is defined.

Syntax and Parameters: SUM ( data )

data: A list of 1 or more numeric items.

Example:

Add 2 to the values in A.

A:A contains [4.33, 1.5, -8.454, 3.567, x]

Formula:

SUM(@A:A, 2)

Results:

6.33, 3.5, -6.454, 5.567, 2

Use the SUMIF function to sum the values in a range that meet a certain condition. This function returns a single value.

Syntax and Parameters: SUMIF( condition, sum range )

condition: A list of true or false values.

sum range: A list of 1 or more numeric items. Non-numeric values return as 0.

Example:

A:A contains [1, 2, 3]

B:B contains [true, false, true]

Formula:

SUMIF(@B:B, @A:A)

Results:

4

Note: If condition has fewer items than sum range, the remaining condition values default to false.

Use CAPITALIZE to change the first letter in each word to uppercase.

Syntax and Parameters: CAPITALIZE ( data )

data: The text value or values

Example:

Capitalize "the 5th dimension".

Formula:

CAPITALIZE("the 5th dimension")

Results:

The 5th Dimension

Use the CONCAT function to join two or more values into one text string.

Syntax and Parameters: CONCAT ( data )

data: The value or values to join together.

Example:

Create a sentence template using data from columns A, B and C.

A:A contains [added, updated],

B:B contains [May 22, 2025, Oct 2, 2025],

C:C contains [2:20pm, 8:12am]

Formula:

CONCAT("The data source was", @A:A, @B:B, "at", @C:C)

Results:

The data source was added May 22, 2025 at 2:20pm

The data source was updated Oct 22, 2025 at 8:12am

Use the CONTAINS function to test each value in the haystack parameter to see if it contains the value in the needle parameter.

Syntax and Parameters: CONTAINS ( haystack, needle )

haystack: A list of 1 or more items.

needle: The case-sensitive item to search for in the haystack parameter.

Example:

Return true for all values that have the letter "a" in them.

A:A contains [Apple, Banana, Orange, Plum]

Formula:

CONTAINS(@A:A, "a")

Results:

false, true, true, false

Use the COUNTRY_CLEAN function to avoid multiple variations of a country’s name and return all the country names in the same form, either as full names or as ISO 3166 standardized names. If a value cannot be identified as a country, INVALID COUNTRY will display.

Syntax and Parameters: COUNTRY_CLEAN (data, [ISO format])

data: A list of countries in text

ISO Format: [optional] ISO 3166-1 alpha-code

Examples:

COUNTRY_CLEAN(ARRAY(“Canada,MX,Canada,CAN,CA,US,United States,USA”))

The result is country names display in their full form, for example, Canada, Mexico, Canada, Canada, Canada, United States, United States, United States.

COUNTRY_CLEAN(ARRAY(“Canada,MX,Canada,CAN,CA,US,United States,USA”),ISO Alpha-2)

The result is country names display using the 2-character ISO code, for example, CA, MX, CA, CA, CA, US, US, US.

COUNTRY_CLEAN(ARRAY(“Canada,MX,Canada,CAN,CA,US,United States,USA”),ISO Alpha-3)

The result is country names display using the 3-character ISO code, for example, CAN, MEX, CAN, CAN, CAN, USA, USA, USA.

Use INDEXOF to search the values in the text parameter for the specified occurrence of the search text (case sensitive) parameter and return the position of where it is found. If search text is not found at the specified occurrence, null is returned.

Syntax and Parameters: INDEXOF ( text, search text, [occurrence] )

text: A list of 1 or more values.

search text: The text string to search for.

occurrence: [optional] The occurrence to search for.

Example:

Return the position of the second occurrence of "at" in column A.

A:A contains [abc, The Cat in the Hat]

Formula:

INDEXOF(@A:A, "at", 2)

Results:

null, 16

Note: INDEXOF is typically used with SUBSTRING to determine where to begin or end the substring.

The JOIN function takes a set of values, joins these values with the glue parameter (a comma is used if glue is not specified), and returns them as a single value.

Syntax and Parameters: JOIN( data, [glue] )

data: A list of 1 or more values.

glue: [optional] A value or symbol to join the values in the data parameter with. By default, a comma is used.

Example:

A:A contains [January, February, March, April, May, June]

Formula:

JOIN(@A:A)

Results:

January,February,March,April,May,June

Use LASTINDEXOF to search the values in the text parameter for the last occurrence of the search text (case sensitive) parameter and return the position of where it is found. If search text is not found at the specified occurrence, null is returned.

Syntax and Parameters: LASTINDEXOF ( text, search text, [occurrence] )

text: A list of 1 or more values.

search text: The text string to search for.

occurrence: [optional] The occurrence to search for.

Example:

Return the position of the last occurrence of "at" in column A.

A:A contains [abc, The Cat in the Hat]

Formula:

LASTINDEXOF(@A:A, "at")

Results:

null, 16

Note: LASTINDEXOF is typically used with SUBSTRING to determine where to begin or end the substring.

Use the LEFT function to return the first character or a specified number of characters starting from the first value on the left.

Syntax and Parameters: LEFT ( text, [number] )

text: A list of 1 or more values.

number:[optional] The number of characters to return.

Example:

A:A contains [Bat Cat, The Cat in the Hat]

Formula:

LEFT(@A:A)

Results:

B

T

Example:

A:A contains [Bat Cat, The Cat in the Hat]

Formula:

LEFT(@A:A, 7)

Results:

Bat Cat

The Cat

Use the LENGTH function to count the number of characters in a text string.

Syntax and Parameters: LENGTH ( text )

text: A list of 1 or more values.

Example:

Count the number of characters in "Hello" and "Hello World".

Formula:

LENGTH(ARRAY("Hello", "Hello World"))

Results:

5, 11

Use the LOWER function to change all characters in the values parameter to lowercase.

Syntax and Parameters: LOWER ( data )

data: A list of 1 or more values. Often used when comparing strings or with the CONTAINS, INDEXOF and LASTINDEXOF functions.

Example:

A:A contains [Bat Cat, The Cat in the Hat]

Formula:

LOWER(@A:A)

Results:

bat cat

the cat in the hat

Use the NUMBERFORMAT function to take a set of values, treat them as numbers, and return them as text with as many digits of decimal places as specified by the precision parameter.

Syntax and Parameters: NUMBERFORMAT ( data, [precision], [separator] )

data: A list of 1 or more items.

precision: [optional] The number of decimal places to return.

separator: [optional] The separator can be set to the desired thousands separator character. The example below sets a comma separator.

Example:

A:A contains [123.456, 1245687, January]

Formula:

NUMBERFORMAT(@A:A)

Results:

123, 1245687, 0

Note: If the data parameter is defined with text values or a blank, the results will be returned as a 0.

Emojis are pictographs most often seen in social media data sources, such as Facebook posts. They may also appear in other data source types. As new emojis are developed, new encoding is used and sometimes this encoding cannot be processed by Klipfolio.

The REMOVE_EMOJI function is used to strip emojis from data so the remaining data can be processed.

Syntax and Parameters: REMOVE_EMOJI ( text )

text: The text field containing the emoji icons.

Example:

Where message and name point to the /data/message and /data/from/name fields in a Facebook data source.

• To display Facebook posts which may contain emojis: REMOVE_EMOJI( message )
• To select specific Facebook posts which may contain emojis: SELECT( REMOVE_EMOJI( message ), ( name = Klipfolio ) )

Note: If the selected data does not contain emojis, the REMOVE_EMOJI function returns the selected data as is.

Use the RIGHT function to return the first character or a specified number of characters starting from the right of the first value.

Syntax and Parameters: RIGHT ( text, [number] )

text: A list of 1 or more values.

number:[optional] The number of characters to return.

Example:

A:A contains [Bat Cat, The Cat in the Hat]

Formula:

RIGHT(@A:A, 7)

Results:

Bat Cat, the Hat

The SUBSTITUTE function replaces a set of characters with another set of characters in a text string. If the occurrence parameter is specified, that occurrence is substituted, otherwise, all occurrences are substituted.

Syntax and Parameters: SUBSTITUTE ( text, old text, new text, [occurrence] )

text: The values to be manipulated.

old text: The values that will be replaced (case sensitive).

new text: The values to replace the values in old text (case sensitive).

occurrence: [optional] Indicates the instance that will be replaced.

Example:

Substitute the 3rd occurrence of the letter a in each word in the data.

A:A contains [Ottawa, banana]

Formula:

SUBSTITUTE(@A:A, "a", "@", 3)

Results:

Ottawa, banan@

Note: While the SUBSTITUTE function is similar to the REPLACE function, the SUBSTITUTE function is used to replace part of a value.

Use SUBSTITUTE_REGEX to substitute text based on a specific pattern, such as location in text.

REGEX (REGular EXPression) is a standard for describing patterns in text. There are several online resources, such as regexr.com., that describe how to define REGEX expressions.

Syntax and Parameters: SUBSTITUTE_REGEX ( text , old text , new text , [occurrence])

text: A list of 1 or more text strings.

old text: The regular expression (case sensitive).

new text: The values to replace the values in old text (case sensitive).

occurrence: [optional] Indicates the instance that will be replaced.

Example:

The regex anchor, ^, is used to match only when 'a' is the first character in a text string.

SUBSTITUTE_REGEX( ARRAY(" apples, banana, orange"), "^a", "x")

The result of this formula is xpple, bread, eggs.

The regex anchor, $, matches the last character in a text string and the pipe (|) indicates 'or'.

SUBSTITUTE_REGEX( ARRAY("apple, banana, orange"), "^a|a$", "x")

The result of this formula is xpple, bananx, orange.

Example:

Substitute all characters until the last dash.

A:A contains [Albany-Orlando-Ottawa]

Formula:

SUBSTITUTE_REGEX(@A:A, ".*-", "@")

Results:

@Ottawa

Use the SUBSTRING function to return a sub-string of text from a string of text.

Syntax and Parameters: SUBSTRING ( text , from , [to] )

text: The text you want the sub-string of.

from: The start position of the sub-string specified as a number.

to: [optional] The end position of the sub-string specified as a number.

Example:

Return data after position 11 in each string.

A:A contains [2025-10-11T1:12:00, 0123456789abcdef]

Formula:

SUBSTRING(@A:A, 11)

Results:

1:12:00, bcdef

Use the TEXT_REVERSE function to reverse the order of characters or numbers in the data.

Syntax and Parameters: TEXT_REVERSE ( data )

data: A list of 1 or more values.

Example:

A:A contains [Canada, United States, Mexico]

Formula:

TEXT_REVERSE(@A:A)

Results:

adanaC, setatS detinU, ocixeM

Use the TRIM_WHITESPACE function to return the text without leading and trailing spaces.

Syntax and Parameters: TRIM_WHITESPACE ( text )

text: A list of 1 or more values.

Example:

TRIM_WHITESPACE (" Reseller, Distributor, Customer ")

The result of this formula returns: Reseller, Distributor, Customer without the leading or trailing spaces.

Example:

Remove leading and trailing spaces around the word " Hello World ".

Formula:

TRIM_WHITESPACE(" Hello World ")

Results:

Hello World

Truncates text to the specified number of characters, starting from the position parameter and optionally inserting a style character.

Syntax and Parameters: TRUNCATE ( text, number, [position], [style] )

text: A list of 1 or more text strings.

number: The position to end the substring, where the first character in text is at position 0.

position: [optional] Identifies where to place the style parameter (start, end, split).

style: [optional] blank, period, ellipsis.

Example:

This example cuts off the text string after 5 characters and adds an ellipsis to the end of the value.

Formula:

TRUNCATE("abcdefghijklmnopqrstuvwxyz", 5, "end", "ellipsis")

Results:

abcde…

Use the UPPER function to change all characters in the values parameter to uppercase.

Syntax and Parameters: UPPER ( data )

data: A list of 1 or more values.

Example:

A:A [Bat Cat, The Cat in the Hat]

Formula:

UPPER(@A:A)

Results:

BAT CAT, THE CAT IN THE HAT

Use the URLDECODE function to decode data that is encoded for use in a URL using URLENCODE, UTF-8, to plain text.

Syntax and Parameters: URLDECODE ( data )

data: 1 or more URL-encoded values.

Example:

A:A contains [www.myCompany.com%2Fbusiness%20users, www.powermetrics.app%2Fintegrations]

Formula:

URLDECODE(@A:A)

Results:

www.myCompany.com/business users, www.powermetrics.app/integrations

Use the URLENCODE function to encode data to be safely used in URLs.

Syntax and Parameters: URLENCODE ( data )

data: 1 or more plaintext values.

Example:

A:A contains [www.myCompany.com/business users, www.powermetrics.app/integrations]

Formula:

URLENCODE(@A:A)

Results:

www.myCompany.com%2Fbusiness+users

www.powermetrics.app%2Fintegrations

Use COUNT_DAYS to return the number of whole days between two dates. You can also exclude a day of the week or a holiday from the results.

Syntax and Parameters: COUNT_DAYS (start, end, [exclude DOW], [holidays], [timezone])

start: The start date to count from. Unix time format.

end: The end date to count until. Unix time format.

exclude DOW: [optional] 1 or more days of the week to exclude from the count. Options include: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

holidays: [optional] 1 or more dates to exclude from the count. Unix time format.

timezone: [optional] The time zone.

Example:

This example counts the number of days between November 1, 2025 and November 10, 2025. Use the DATE function to convert the dates to Unix time format.

Formula:

COUNT_DAYS(DATE("2025/11/01","yyyy/MM/dd"),DATE("2025/11/10","yyyy/MM/dd") )

Results:

10

Example:

This example counts the number of days between November 1, 2025 and November 10, 2025 just like the example above. Use the DATE function to convert the dates to Unix time format. Additionally, this example excludes both Saturday and Sunday from the count.

Formula:

COUNT_DAYS(DATE("11/01/2025","MM/dd/yyyy"),DATE("11/10/2025","MM/dd/yyyy"), ARRAY("Sun","Sat"))

Results:

7

Use DATE to convert dates specified in a given format into Unix time format.

Syntax and Parameters: DATE ( dates , format , [timezone] )

dates: A list of 1 or more date/time values.

format: The date format of the values in the dates parameter

timezone: The time zone.

Formula:

DATE("2025/01/09", "yyyy/MM/dd")

Results:

1736398800

Use DATE_ADD to add or subtract time to or from given date/time values. Results are returned in Unix time format.

Syntax and Parameters: DATE_ADD ( values , unit , amount, [timezone] )

values: A list of 1 or more values in Unix time format.

unit: The unit to add to the date. Options include: second, minute, hour, day, week, month, quarter, year.

amount: The number of units to add. Use a negative number to subtract units.

timezone: [optional] The time zone.

Example:

DATE_ADD(DATE("2025/01/01","yyyy/MM/dd"), day,6)

Result:

1736226000

Notes:

Unix time format is a numeric representation of the date as a number of seconds and is required when performing calculations or comparisons on dates. Use a Unix time converter to convert timestamps.

The default account level time zone is used for all date/time functions but can be overridden by using the time zone parameter in functions.

Use the DATE_CLOSEST function to find the closest day of the week (in Unix time format) before or after a given date.

Syntax and Parameters: DATE_CLOSEST( values , dow , [direction], [timezone])

values: A list of 1 or more values in Unix time format.

dow: Day of the week. Options include: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

direction: [optional] The direction. Options include forward or backward.

timezone: [optional] The time zone.

Example:

DATE_CLOSEST(DATE("2025/01/01","yyyy/MM/dd"),Monday,forward)

Result:

1736139600

Use the DATE_CONVERT function to convert values from one date format to another date format.

Syntax and Parameters: DATE_CONVERT ( values , format in , format out )

values: A list of 1 or more date/time values.

format in: The current date format of the date/time values.

format out: The date/time format you want to convert the dates to.

Example:

DATE_CONVERT("9/11/2025", "d/M/yyyy", "MMM dd, yyyy")

Result:

Nov 09, 2025

Use DATE_ENDOF to return the date in the values parameter at the end of the given unit parameter.

Syntax and Parameters: DATE_ENDOF ( values, unit, [relative value], [first], [timezone] )

values: A list of 1 or more values in Unix time format.

unit: The unit to return the values in. Options include week, month, quarter, year.

relative value: [optional] Number of units to add or subtract.

first: [optional] The starting point for the unit parameter, when the unit parameter is set to week or year (not applicable otherwise). Note: If you specify the first value, you must also provide a relative value.

Options include Sunday (default), Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

Options include January (default), February, March, April, May, June, July, August, September, October, November, December.

timezone: [optional] The time zone.

Example:

DATE_ENDOF(DATE("2025/01/01","yyyy/MM/dd"),month)

Result:

1738299600

Example:

DATE_ENDOF(DATE("2025/01/01","yyyy/MM/dd"),month,-1)

Result:

1735621200

Use DATE_IN to return true or false depending on whether or not the given dates fall within the specified time period.

Syntax and Parameters: DATE_IN ( values, unit, [relative value], [first], [timezone] )

values: A list of 1 or more values in Unix time format.

unit: The unit to return the values in. Options include week, month, quarter, year.

relative value: [optional] Number of units to add or subtract.

first: [optional] The starting point for the unit parameter, when the unit parameter is set to week or year (not applicable otherwise). Note: If you specify the first value, you must also provide a relative value.

Options include Sunday (default), Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

Options include January (default), February, March, April, May, June, July, August, September, October, November, December.

timezone: [optional]

Example:

Check if today is in last month.

Formula:

DATE_IN(DATE("2025/01/01","yyyy/MM/dd"),year)

Results:

false

Use DATERANGE to return a list of dates between and including the start and end dates. If start is later than end, 0 items are returned.

Syntax and Parameters: DATERANGE ( start, end, [format] )

start: A list of 1 or more values in Unix time format.

end: last date in range.

format: [optional] required if start and end dates are in a format other than the default or in Unix time (default input is M/d/yyyy, default output is MM/dd/yyyy).

Example:

Return all dates between 05/01/2025 and 05/04/2025

Formula:

DATERANGE("05/01/2025", "05/04/2025")

Results:

05/01/2025, 05/02/2025, 05/03/2025, 05/04/2025

Use DATE_SET to set a specified date unit to a set value.

Syntax and Parameters: DATE_SET ( values, unit, unit value, [timezone] )

values: A list of 1 or more values in Unix time format.

unit: Options include second, minute, hour, day, month, year.

unit value: The value to be assigned to the unit parameter.

timezone: [optional] The time zone.

Example:

DATE_SET(DATE("2025/01/16","yyyy/MM/dd"),day,8)

Result:

1740027600

Use DATE_STARTOF to return the date in the values parameter at the start of the given unit parameter.

Syntax and Parameters: DATE_STARTOF ( values, unit, [relative value], [first], [timezone] )

values: A list of 1 or more values in Unix time format.

unit: The unit to return the values in. Options include week, month, quarter, year.

relative value: [optional] Number of units to add or subtract.

first: [optional] The starting point for the unit parameter, when the unit parameter is set to week or year (not applicable otherwise). Note: If you specify the first value, you must also provide a relative value.

Options include Sunday (default), Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

Options include January (default), February, March, April, May, June, July, August, September, October, November, December.

timezone: [optional] The time zone.

Example:

DATE_STARTOF(DATE("2025/01/16","yyyy/MM/dd"),month)

Result:

1735707600

Use DATE_UNITVALUE to return the value of the specified unit from the given date/time values.

Syntax and Parameters: DATE_UNITVALUE ( values, unit, [first], [timezone] )

values: A list of 1 or more values in Unix time format.

unit: The unit to return the values in. Options include second, minute, hour, day, day of week, day of year, week, week of month, month, quarter, year.

first: [optional] The starting point for the unit parameter, when the unit parameter is set to week or year (not applicable otherwise).

Options include Sunday (default), Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

Options include January (default), February, March, April, May, June, July, August, September, October, November, December.

timezone: [optional] The time zone.

Example:

DATE_UNITVALUE(DATE("2025/01/16","yyyy/MM/dd"),"week of the month")

Result:

3

Use DATEVALUE to convert dates that are in Unix time format to a specified (human readable) format.

Syntax and Parameters: DATEVALUE ( dates, [format], [timezone] )

dates: A list of 1 or more values in Unix time format.

format: [optional] The format you want to convert your dates to.

timezone: [optional] The time zone.

Example:

DATEVALUE(1769749200)

Result:

01/30/2026

Use NOW to return the current date and time in Unix time format. Milliseconds are given as 3 decimal places.

Syntax and Parameters: NOW ( [timezone] )

Example:

Return the value, 30 January 2026 21:56:43 GMT, in Unix time format.

Formula:

NOW()

Results:

1769802413.581

Example:

In these examples, NOW is wrapped in the DATEVALUE function so that the current date and time are returned in the format provided instead of Unix time format.

Formula:

DATEVALUE( NOW(), "dd-MM-yyyy")

Result:

20-11-2025

Formula:

DATEVALUE( NOW(), "h:mm:ss" )

Result:

9:39:57

Use the TIME function to convert a date/time duration, specified as a combination of days, hours, minutes and seconds, to number of seconds.

Syntax and Parameters: TIME ( values, format )

values: A list of 1 or more date/time durations.

format: The format of the date/time duration.

Supported formats include:

• hh:mm:ss
• hh:mm
• dd:hh:mm:ss
• dd:hh:mm
• dd:hh
• d
• h
• m
• s
• ss
• dd
• mm
• hh
• ss
• mmss
• ddhhmmss

Example:

Returns the number of seconds.

Formula:

TIME("1:47:67:800", "d:h:m:s")

Results:

260,420 seconds

Use the TODAY function to return the Unix time format date for today's date.

Syntax and Parameters: TODAY ( [timezone] )

Example:

At 30 January 2026 12:08:43 GMT.

Formula:

TODAY()

Results:

1769749200

Use the YESTERDAY function to return the Unix time format date for yesterday's date.

Syntax and Parameters: YESTERDAY ( [timezone] )

Example:

Returns the Unix time for yesterday at Jan 30, 2026 12:08:43 GMT.

Formula:

YESTERDAY()

Results:

1769662800

ERF computes the standard error function integrated between the lower limit and upper limit, or between the lower limit and infinity if no upper limit is provided.

Parameters:

lower limit

upper limit: [optional]

ERFC computes the complement of the standard error function integrated between the lower limit and infinity.

Parameters:

lower limit

MA_CUMULATIVE returns the cumulative moving average for each of the given values.

Parameters:

values

MA_EXPONENTIAL returns the exponential moving average for each of the given values using the given period "n".

Parameters:

values n

MA_SIMPLE returns the simple moving average for the previous "n" data points for each of the given values.

Parameters:

values n

Use NORMSDIST to take a single value or a list of values and return the normal standard distribution of each value provided.

Parameters:

value

Use RANK to return the rank of each item according to the order of values in a list of data.

Syntax and Parameters: RANK ( data, [order] )

data: A list of 1 or more values.

order:[optional] The order to rank the values in. Options include ascending or descending.

Example:

Rank the values in column A. A:A contains [10, 5, 75]

Formula:

RANK(@A:A)

Results:

2, 1, 3

Use SLOPE to calculate the slope of the linear regression line represented by columns of known x and y values.

Parameters:

known_y

known_x

Use STANDARDIZE to standardize each value in a column of values using a supplied mean and a supplied standard deviation.

Parameters:

values

mean

stdev

STDEV computes the standard deviation for a random sampled population using the (n-1) method.

Parameters:

population

STDEVP computes the standard deviation for a complete population using the (n) method.

Parameters:

population

Use VARIANCE to compute the variance for a random sample population of values (n-1) method.

Parameters:

population

Use VARIANCEP to compute the variance for a complete population of values (n) method.

Parameters:

population

Use ZTEST to compute the one-tailed probability-value of a z-test. For a given hypothesized population mean, μ0, ZTEST returns the probability that the sample mean would be greater than the average of observations in the data set (array) - that is, the observed sample mean.

Parameters:

population

μ

sigma [optional]

Example:

Check whether a group’s average score is different from 70.

Column A contains [65,68,70,72,74,69,71,73,67,75]

Formula:

ZTEST(A:A, 70)

Syntax: kf:element_at( object, index )

Select an element at the position specified by index. This function is used when a field name is unnamed and is instead referenced by number (position).

For example, using the following data source:

Example 1:

@ kf:element_at(rows,1)

Returns:

• /nl/
• /studyScreen?sourceId=11263
• /studyScreen?sourceId=270814
• /studyScreen?sourceId=604

Example 2:

@ kf:element_at(rows,2)

Returns:

• 1
• 1
• 1
• 1

Syntax:kf:fill_elements(path,'fieldname')

Note: You can enter the formula manually or generate it automatically by choosing the Selection Option Select all <field_name> elements in all <parent> elements, adding blanks for missing <field_name> elements.

This function is used to “fill in” blanks for an element that is not included in every record. This is useful for data alignment.

For example, using the following data source:

Example 1:

@ /player/first-name Returns: Anders, Maurice, Wayne

@ /player/nickname Returns: Rocket, The Great One

As is, the data incorrectly aligns Anders with the nickname, Rocket, and Maurice with the nickname, The Great One.

Use kf:fill_elements to align this data correctly:

@ kf:fill_elements(/player,'nickname')

Returns: <blank>, Rocket, The Great One

Example 2:

@ kf:fill_elements(/player/team,'wha')

Returns: 1974-1978, <blank>, 1978-1979

Note: This function does not work for nested arrays.

Syntax: kf:names(object)

Note: You can enter the formula manually or generate it automatically by choosing the Selection Option Select all field names in <parent> element.

The kf:names function returns the names of all fields contained in an element. This is useful when the field names identify categories such as demographics or countries.

Example 1:

For example, Facebook can return page fans data by gender and age. To create a list of these categories for a table column for a bar/line chart x-axis, use:

@ kf:names(data/values/value)

Which returns data shown in the following image:

Compare with @ kf:names(/data/values/value/M.25-34) which returns only value M.25-34

More examples:

The following show more examples using the data source featured in the previous function (kf:fill_elements).

@ kf:names(/player[1]/team) Returns: name, wha

@ kf:names(/player/team) Returns: names for all items in player: name, wha, name, nhl, name, wha, nhl

@ kf:names(/player[1]) Returns: first_name, last_name, team

Note: Unnamed fields cannot be selected directly; see kf:element_at (described at the top of this table).