Calculated Fields Reference

This article lists all of the functions that can be used to create calculated fields and measures in an Ad Hoc view in Lambda Analytics.

 

Name Description Example
Absolute (NumericExpression) This returns the absolute value of a number or field, that is, the non-negative value of the number. Absolute("Transaction Amount"): Shows the magnitude of each transaction, regardless of whether the transaction is positive or negative. 
Average (NumericExpression[,'Level'])

This returns the average value of a measure or numeric field, based on an optional level.

Null values are not included 

Average("Grades," "RowGroup")
Boolean ('StringExpression')

Casting function that takes a string expression and converts it to a Boolean data type.

The string can be any expression that returns a supported string, including a field value or an attribute retrieved with the Attribute function.

The Boolean function requires one of the following strings: true, false, True, False, TRUE, or FALSE. Other strings will return an error.

Boolean('true')
Case (Expression, ValueExpression1, 
ReturnExpression1, ValueExpression2, 
ReturnExpression2[,..., 
ValueExpressionN,ReturnExpressionN]
[,DefaultReturnExpression])

An expression followed by one or more value expression/return expression pairs, with an optional final return expression.

This compares the expression in the first argument to each value expression in order of appearance. This will then return the value of the expression immediately following the first value expression that matches. 

If no expression matches, this will instead return the final DefaultReturnExpression if present. Null otherwise.

The types of all the return expressions must be compatible.

Case("Shipped by", 1, 'FedEx', 2, 'UPS', 3, 'USPS', 'Unknown')
CaseRange (NumericExpressionInput, NumericExpression1, 
ReturnExpression1, NumericExpression2, 
ReturnExpression2[,...,NumericExpressionN, 
ReturnExpressionN][,DefaultReturnExpression])

An expression followed by one or more numeric expression/return expression pairs, with an optional final return expression. This finds the first numeric expression that is greater than the input expression, and returns the value of the corresponding return expression. 

If no expression is greater, return the final DefaultReturnExpression if present. Null otherwise.

The types of all the return expressions must be compatible. 

CaseRange(CountAll("Shipping charge") % CountAll("Shipping charge", 'Total'), 2.0, 'Less than 2%', 5.0, '2%-5%', 'More than 5%')
CaseWhen (BooleanExpression1, ReturnExpression1, 
BooleanExpression2, ReturnExpression2[,..., 
BooleanExpressionN,ReturnExpressionN [, DefaultReturnExpression])

One or more pairs of Boolean expressions followed by return expressions, with an optional final return expression. This returns the expression immediately following the first true Boolean expression. 

If no expression is true, returns the final DefaultReturnExpression if present. Null otherwise.

This is the most flexible construct.

The types of all the return expressions must be compatible. 

CaseWhen("Shipped by" ==1, 'FedEx', "Shipped by" == 2, 'UPS', "Shipped by" ==3, 'USPS', 'Unknown')
Concatenate (TextExpression1[,TextExpression2,...,TextExpressionk])

This combines multiple text strings and/or fields into a single text field. Text strings are enclosed in single quotes; labels for fields or measures in Ad Hoc views are enclosed in straight quotes.

Concatenate("Last Name", ' , ' , "First Name")

Concatenate("Product Category", '--', "Product Name")

Contains (TextExpression1, [TextExpression2])

Boolean that returns true if the first string contains the second, false otherwise.

Contains("Product Name", 'Soda')

CountAll (Expression[,'Level']))

This rturns the count of non-null items in a field or measure.

Note that CountAllalways returns a non-negative integer. Level 

CountAll("Transaction Amount", 'RowGroup'): Counts the total number of non-null transactions in the specified group.

CountDistinct (Expression[,'Level'])

This returns the distinct count of non-null items in the input. Always 

CountDistinct("Customer Name", 'Total'): Counts the number of distinct customers.

Date ('StringExpression')

Casting function that takes a string expression and converts it to a date data type. 

The string can be any expression that returns a supported string, including a field value or an attribute retrieved with the Attribute function. 

The Date function requires a string value formatted as 'yyyy-mm-dd." Other strings will return an error.

Date('2020-12-15')

Decimal ('StringExpression')

Casting function that takes a string expression and converts it to a decimal data type.

The string can be any expression that returns a supported string, including a field value or an attribute retrieved with the Attribute function.

The Decimal function requires a string value in decimal format.

Decimal('1234.567')

DayName (DateExpression)

Given a date field, this returns a text field with the name of the day of the week.

DayName("Open Date"): Displays the day of the week on which the store was opened.

Mode(DayName("Open Date"),'Total'): Displays the day of the week on which the most stores were opened.

DayNumber (DateExpression)

Numeric field that returns the day of the month from a date field.

DayNumber("Open Date"): Displays the day of the month on which a store was opened.

ElapsedDays (DateExpression1, DateExpression2)

This calculates the number of days elapsed between two date fields that contain time values.

ElapsedDays("Date shipped","Date required")

ElapsedHours (DateTimeExpression1, DateTimeExpression2)

This calculates the number of hours elapsed between two date fields that contain time values.

ElapsedHours("Date shipped","Date required")

ElapsedHours 
(DateTimeExpression1, DateTimeExpression2)

This calculates the number of hours elapsed between two date fields that contain time values.

ElapsedHours("Date shipped","Date required")

ElapsedMinutes 
(DateTimeExpression1,DateTimeExpression2)

This calculates the number of minutes elapsed between two date fields that contain time values.

ElapsedMinutes("Date shipped","Date required")

ElapsedMonths 
(DateTimeExpression1,DateTimeExpression2)

This calculates the number of months elapsed between two date fields that contain time values.

ElapsedMonths("Date shipped","Date required")

ElapsedQuarters 
(DateExpression1, DateExpression2)

This calculates the number of quarters elapsed between two date fields.

ElapsedQuarters("Date shipped","Date required")

ElapsedSeconds 
(DateTimeExpression1,DateTimeExpression2)

This calculates the number of seconds elapsed between two date fields that contain time values.

ElapsedSeconds("Date shipped","Date required")

ElapsedSemis 
(DateExpression1,DateExpression2)

This calculates the number of semi-years elapsed between two date fields.

ElapsedSemis("Date shipped","Date required")

ElapsedWeeks 
(DateExpression1,DateExpression2)

This calculates the number of weeks elapsed between two date fields that contain time values.

ElapsedWeeks("Date shipped","Date required")

ElapsedYears 
(DateExpression1,DateExpression2)

This calculates the number of years between two date fields.

ElapsedYears("Date shipped","Date required")

EndsWith 
(TextExpression1, TextExpression2

Boolean that returns true if the first text input ends with the string specified in the second input; false otherwise.

EndsWith("Product Name", 's')

IF (BooleanExpression, ExpressionWhenTrue[, ExpressionWhenFalse])

If given a Boolean field or calculation as the first argument, this will return the second argument if true; optionally returns the third argument if false. Returns null if the first argument is null. 

ExpressionWhenFalse must be of the same type as ExpressionWhenTrue. If ExpressionWhenFalse is not set, then a false result returns a null value.

You can create a BooleanExpression using the comparison operators, any functions that return Boolean values, and logical operators.

When dates are used in comparisons or the IF function, they must be the same type.

IF(Contains("Proudct Name", 'Soda'), 'Yes', 'No'): Uses the Contains function to see whether the product name contains the string "Soda." If it does, it sets the field value to 'Yes.'

Integer ('StringExpression')

Casting function that takes a string expression and convert it to an integer data type. The string can be any expression that returns a supported string, including a field value or an attribute retrieved with the Attribute function. 

The Integer function requires a string value that can be read as an integer, such as '123.' Other strings will return an error.

Integer('123')

IsNull 
(Expression)

Boolean that returns true if the field value is null; false otherwise.

IsNull("First Name")

Length 
(TextExpression)

Given a text string, this returns its length. Null values return null.

Length("First Name")

Max 
(NumericExpression|DateExpression[,'Level'])

This returns the maximum value reached by the specified field or calculation.

Level can be one of the following: 
Current(default), ColumnGroup, ColumnTotal, RowGroup, RowTotal, Total.

Max("Grade")

Median 
(NumericExpression|DateExpression[,'Level'])

For an odd number of values, this returns the middle value after all values are listed in order.

For an even number of values, this will return the average of the middle two values.

Level can be one of the following: Current(default), ColumnGroup, ColumnTotal, RowGroup, RowTotal, Total.

Median("Grade")

Mid 
(TextExpression,Integer1,Integer2)

Given a text string, this returns the substring starting at Integer1 with length Integer2.

Mid("Phone", 1, 3): Given an American phone number starting with a 3-digit area code, extracts the area code.

Min 
(NumericExpression|DateExpression[,'Level'])

This returns the minimum value reached by the specified field or calculation based on an optional level.

Level can 
be one of the following: Current(default), ColumnGroup, ColumnTotal, RowGroup, RowTotal, Total.

Min("Grade")

Mode 
(Expression[,'Level'])

This returns the most frequent value reached by the specified input, based on an optional level.

Level can be one of the following: 
Current(default), ColumnGroup, ColumnTotal, RowGroup, RowTotal, Total.

Mode (DayName ("Order Date",RowGroup)): For each row group, returns the day of the week on which the most orders were placed.

MonthName 
(DateExpression)

This returns a text field with the name of the month.

MonthName ("Order Date" )

MonthNumber (DateExpression)

This returns the number of the month, with January = 1 and December = 12.

Null values return null.

MonthNumber ("Order Date")

PercentOf (NumericExpression[,'Level'])

This returns the value as a percent of total for the specified level. Null values are ignored. Note that possible values for Level are ColumnGroup, ColumnTotal, RowGroup, RowTotal, Total(default). 

Note: Calculated fields using the PercentOf function should not be used as filters; if PercentOf is used as a filter, then the total percent may not be 100.

PercentOf("")

Range (NumericExpression[,'Level'])

This returns the difference between the largest and smallest values of the given input.

Range("Salary",'ColumnGroup')

Rank 
(NumericExpression)

This returns the position of each value relative to the other values after all the values are listed in order.

Rank("Store Sales")

Round (NumericExpression[,Integer])

This rounds a number to a specified number of digits; default is zero (0) digits. Decimal values greater than 0.5 are rounded to the next largest whole number, and values less than 0.5 are rounded down.

Round("Sales")

StartsWith 
(TextExpression1, TextExpression2]

Boolean that returns true if the first text input starts with the string specified in the second input; false otherwise.

StartsWith("Product Name", 'Q')

StdevP (NumericExpression[,'Level'])

Standard deviation based on the entire population, taken over the values at the specified (optional) level. Null values are excluded.

Level can be one of the following: Current(default), ColumnGroup, ColumnTotal, RowGroup, 
RowTotal, Total.

StdevP("Sales",'RowTotal')

StdevS (NumericExpression[,'Level'])

Standard deviation based on a sample, taken over the values at the specified level. Null values are excluded.

Level can be one of the following: Current (default), ColumnGroup, ColumnTotal, RowGroup,RowTotal, Total.

StdevS("Sales",'RowTotal')

StripTags ("TextFieldName", ")

Replaces all HTML tags from a specified field with the value specified in the second parameter. 
For example, if the specified "summary" field reads as:

<h1><span class="label">Onboarding Fundamentals</span></h1>

and a calculated field is created with the StripTags function as:

StripTags("summary", ")

The StripTags function renders the field as "Onboarding Fundamentals"—as the second parameter is a blank space, the function will replace all HTML tags with the value " ", while preserving the natural text.

StripTags("summary", '')

Sum (NumericExpression[,'Level'])

This returns the sum of all values in the range. Null values are excluded.

Level can be one of the following: Current (default), ColumnGroup, ColumnTotal, RowGroup, RowTotal, Total.

Sum("Sales",'RowGroup')

Time ('StringExpression')

Casting function that takes a string expression in the format 'HH:mm:ss.SSS' and converts it to a time data type.

The string can be any expression that returns a valid string, including a field value or an attribute retrieved with the Attribute function.

Time(17:12:33:147')
Timestamp ('StringExpression')

Casting function that takes a string expression in the format 'yyy-MM-dd HH:mm:ss.SSS' and converts it to a timestamp data type.

The string can be any expression that returns a valid string, including a field value or an attribute retrieved with the attribute function.

Timestamp('2020-12-15 17:12:33:147')

Today 
(Integer)

This calculates the date that is the specified number of days from the current system date.

Today (0): The current system date. 

Today(1): The day after the current system date. 

Today(-1): The day before the current system date.

WeightedAverage (NumericExpression1,NumericExpression2,'Level')

This returns the weighted average for the first input weighted with respect to the second input, calculated at an optional level. Null values are excluded.

Level can be one of the following: Current(default), ColumnGroup, ColumnTotal, RowGroup, RowTotal, Total.

WeightedAverage ("Price","Units", 'Current'): The extended price based on the number of units. 

WeightedAverage ("Price","Units", 'RowGroup'): The sum of the extended price for all units in the row group.

Year (DateExpression)

Given a date field, this returns the year.

Year("Order Date" )