Calculated Fields
Overview
A field is a column in a database table. A calculated field is a field that uses existing database fields and applies additional logic — it allows you to create new data from your existing data.
A calculated field either:
 performs some calculation on database fields to create a value that is not directly stored in the database or
 selects values in database fields based on some customized criteria
When to Use Calculated Fields
Calculated fields lend to more flexibility and efficiency in your analyses.
 Power multiple visual analyses with one query  You can now create multiple calculations on top of one Helixpowered query. Whether you’re exploring your data or answering followup questions from your stakeholders, you’ll no longer have to revisit your multiple SQL queries multiple times.
 Take full advantage of filtering and drill down features  Previously, some aggregations of preaggregated SQL fields led to incorrect results.
 Empower nonSQL stakeholders  People who aren’t familiar with SQL but perhaps are familiar with similar tools like Tableau can now answer their own questions.
Some common scenarios for when you’d want to use calculated fields include:
 The metrics you need for your analysis are not directly stored in your data warehouse.
 You want to transform values for your visualization.
 You want to quickly aggregate or filter your data.
Creating a Calculated Field
 Navigate to the Mode home page and sign in to your organization.
 Click the green + to create a new report in the upper righthand corner.
 Run a SQL query. (It can be as simple as SELECT * FROM table.)
 Create a new chart.
 Open the calculated field Formula Editor.

Type in a name and formula for your calculated field. This example uses the formula:
SUM(CASE [Status] WHEN 'CANCELLED' THEN 1 ELSE 0 END)/SUM(1)
This formula checks for whether the order status was cancelled. It will sum up the tally of cancelled orders and divide by the total number of tows to calculate the cancellation rate.
TIP: To see the full list of functions Mode currently supports, open the panel on the righthand side.
 When you’re done, hit Apply or Done.
Congratulations, you have now created your first calculated field! You should see it in your fields list, with an equal sign (=) next to the data type icon to indicate that it is a calculated field.
Using a Calculated Field
In charts
You can chart your calculated field just as you could a SQLgenerated field, by selecting and dragging in the field into your Chart menu.
In filters
You can also filter your calculated field just as you could a SQLgenerated field.
Calculated Field Best Practices
Calculation building blocks
These are the four basic components that make up any calculated field:
 Fields  columns from your data source, can be either a Dimension or a Measure.
 Operators  symbols that denote a certain operation, like
+
and
.  Functions  transforms the given input to an expected output, like
COUNT()
andSUM()
.  Literal expressions  constant values that are represented as is. This includes numbers (
1
), strings ("This is a string"
), dates (#20200601#
), booleans (true
), andnull
.
Additionally, calculated fields can also contain:
 Parameters  fixed values that functions expect as input, such as
'week'
inDATEPART()
.  Comments  notes or commentary about the calculation that will not be included in the computation. Comments in calculated fields are always marked by a prepended
//
.
Field properties
Property  Description  In Mode 

Dimension  Fields that are used to slice and describe data records (e.g. names, dates, )  
Measure  Typically the values corresponding to the dimension that will be aggregated (e.g. sum, count, average)  
Discrete  Values in the dataset are distinct and separate. These fields are indicated in Mode with blue icons.  
Continuous  Values in the dataset can take on any value within a finite or infinite range. These fields are indicated in Mode with green icons. 
Available Operators:
Precedence  Symbol  Name  Description  Example 

1   (negate)  Negate  Negates the numeric input  1 
2  *  Multiplication  Multiplies two numeric types together  5 * 4 = 20 
3  /  Division  Divides the first numeric input by the second numeric input  20 / 4 = 5 
4  +  Addition  Adds two numeric types together  2 + 2 = 4 
4   (subtract) 
Subtraction  Subtracts two numeric types  10  8 = 2 
5  =  Equal to  Compares two numbers, dates, or strings and returns either TRUE, FALSE, or NULL.  5 + 5 == 10 
5  >  Greater than  Compares two numbers, dates, or strings and returns either TRUE, FALSE, or NULL.  6 > 5 = TRUE 
5  <  Less than  Compares two numbers, dates, or strings and returns either TRUE, FALSE, or NULL.  6 < 5 = False 
5  >=  Greater than or equal to  Compares two numbers, dates, or strings and returns either TRUE, FALSE, or NULL.  5 >= 5 = TRUE 
5  <=  Less than or equal to  Compares two numbers, dates, or strings and returns either TRUE, FALSE, or NULL.  4 <= 5 = TRUE 
5  <>  Not equal to  Compares two numbers, dates, or strings and returns either TRUE, FALSE, or NULL.  5 != 'five' = TRUE 
6  NOT  Not  Negates the boolean or expression  NOT FALSE = TRUE 
7  AND  And  An expression or boolean must evaluate to TRUE on both sides of the AND  TRUE AND FALSE = FALSE 
8  OR  Or  An expression or boolean must evaluate to TRUE on at least one side of the OR  TRUE OR FALSE = TRUE 
Precedence dictates the order in which operators will be evaluated in a formula. Parentheses can be used to change the order of precedence.
Available functions
Number
Function  Description  Examples 

ABS(<number>) 
Returns the absolute number of the given number.  ABS(4) = 4 ABS([Elevation]) 
CEILING(<number>) 
Rounds a number to the nearest integer of greater than or equal value.  CEILING(3.14159) = 4 CEILING([Order Price]) 
EXP(<number>) 
Returns e raised to the power of the given number, where e is the Euler’s constant 2.718…  EXP(2) = e^2 EXP([Order Quantity]) 
FLOOR(<number>) 
Rounds a number to the nearest integer of less than or equal value.  FLOOR(3.14159) = 3 FLOOR([Order Price]) 
LOG10(<number>) 
Returns the base 10 logarithm of a number.  LOG10(100) = 2 LOG10([Order Quantity]) 
LN(<number>) 
Returns the natural logarithm of a number, where the base is Euler’s constant e.  LN(EXP(2)) = 2 LN([Order Quantity]) 
MOD(<number>, <number>) 
Divides the first number by the second number and returns their remainder.  MOD(11, 2) = 1 MOD([Order Quantity], 100) 
POWER(<base number>, <exponent number>) 
Returns the base raised to the inputted exponent power.  POWER(2, 3) = 8 POWER([Order Quantity], [Price]) 
SQRT(<number>) 
Returns the square root of the given number.  SQRT(9) = 3 SQRT([Order Quantity]) 
TRUNC(<number>, <number>) 
Returns the number cut off to the specified decimal place.  TRUNC(3.14159, 2) = 3.14 TRUNC(AVG([Profit]), 2) 
ZN(<expression>) 
Returns the given expression if not NULL, otherwise returns 0.  ZN(1, NULL, 1) = 1, 0, 1 ZN[Order Quantity]) 
String
Function  Description  Examples 

CONTAINS(<string>, <substring>) 
Returns true if the substring is within the string, otherwise returns false.  CONTAINS('Hello world!', 'lo w') = True CONTAINS('Hello world!' 'hello') = False CONTAINS([status], 'error') 
Datetime
Function  Description  Examples 

DATEADD(<datepart>, <interval>, <datetime>) 
Adds the specified datepart to the given datetime, where  DATEADD('week', 4, TODAY()) = #20200629# DATEADD('quarter', 1, [Date]) 
DATEDIFF(<datepart>, <datetime1>, <datetime2>) 
Finds the difference between the the two datetimes expressed in units of the given datepart.  DATEDIFF('day', #20200601#, #20200629#) = 28 DATEDIFF('month', [Start Date], [End Date]) 
DATEPART(<datepart>, <datetime>) 
Returns the datepart of the datetime, expressed as an integer.  DATEPART('day', #20200601#) = 1 DATEPART('month', #20200601#) = 6 DATEPART('year', #20200601#) = 2020 DATEPART('week', [Order Date]) 
DATETRUNC(<datepart>, <datetime>) 
Returns the datetime truncated to the nearest datepart. If the datepart is week , the default is Sunday. 
DATETRUNC('month', #20200629#) = #20200601# DATETRUNC('quarter', [Order Date]) 
NOW() 
Returns the current datetime.  NOW() = #20200601 9:00:00 AM# 
TODAY() 
Returns the current date. 
TODAY() = #20200601# 
Possible <datepart>
values include:
second
minute
hour
day
week
weekday
month
dayofyear
quarter
year
Logical
Function  Description  Examples 

<expression1> AND <expression2> 
Returns true if and only if both expressions are true.  2 > 1 AND 2 > 3 = False [Order Date] >= TODAY() AND [Order Amount] > 1 
CASE <expression> WHEN <value1> THEN <return1> [WHEN <value2> THEN <return2> ...] ELSE <default return> END 
Performs a series of logical tests for equality and returns the value of the test that first evaluated to true.  CASE [Status] WHEN 'Completed' THEN 1 WHEN 'Cancelled' THEN 0 ELSE NULL END 
IF <expression> THEN <return1> [ELSEIF <expression2> THEN <return2> ...] ELSE <default return> END 
Performs a series of logical tests, not necessarily always for equality, and returns the value of the test that first evaluated to true.  IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Nonprofitable' END 
<expression1> OR <expression2> 
Returns true as long as oneof the expressions is true. 
2 > 1 OR 2 > 3 = True [Order Amount] >= 5 OR [Price] >= 50 
Aggregate
Function  Description  Example 

AVG(<expression>) 
Averages the values of items ina group, not including NULL values. 
AVG(1, 2, 3, 10) = 4 AVG([Order Amount]) 
COUNT(<expression>) 
Counts the total number of items in a group, not including NULL values. 
COUNT([1, 2, 2, 4]) = 4 COUNT([Order Id]) 
COUNTD(<expression>) 
Counts the total number of distinct items in a group, not including NULL values.  COUNTD([1, 2, 2, 4]) = 3 COUNTD([Order Id]) 
MAX(<expression>) 
Computes the item in the group with the largest numeric value.  MAX([1, 2, 2, 4]) = 4 MAX([Order Amount]) 
MEDIAN(<expression>) 
Computes the median of an expression, which is the value that the values in the expression are below 50% of the time.  MEDIAN([1, 2, 3, 4, 5]) = 3 MEDIAN([1, 2, 3, 10]) = 2.5 MEDIAN([Order Amount]) 
MIN(<expression>) 
Computes the item in the group with the smallest numeric value.  MIN([1, 2, 2, 4]) = 1 MIN([Order Amount]) 
PERCENTILE_1(<expression>) 
Computes the 1st percentile within an expression, which is the value that the values in the expression are below 1% of the time.  PERCENTILE_1([1, 2, 3, 4, 5]) = 1.04 PERCENTILE_1([Order Amount]) 
PERCENTILE_5(<expression>) 
Computes the 5th percentile within an expression, which is the value that the values in the expression are below 5% of the time.  PERCENTILE_5([1, 2, 3, 4, 5]) = 1.2 PERCENTILE_5([Order Amount]) 
PERCENTILE_25(<expression>) 
Computes the 25th percentile within an expression, which is the value that the values in the expression are below 25% of the time.  PERCENTILE_25([1, 2, 3, 4, 5]) = 2 PERCENTILE_25([Order Amount]) 
PERCENTILE_75(<expression>) 
Computes the 75th percentile within an expression, which is the value that the values in the expression are below 75% of the time.  PERCENTILE_75([1, 2, 3, 4, 5]) = 4 PERCENTILE([Order Amount]) 
PERCENTILE_95(<expression>) 
Computes the 95th percentile within an expression, which is the value that the values in the expression are below 95% of the time.  PERCENTILE_95([1, 2, 3, 4, 5]) = 4.8 PERCENTILE_95([Order Amount]) 
PERCENTILE_99(<expression>) 
Computes the 99th percentile within an expression, which is the value that the values in the expression are below 99% of the time.  PERCENTILE_99([1, 2, 3, 4, 5]) = 4.96 PERCENTILE_99([Order Amount]) 
SUM(<expression>) 
Sums the total number of items in a group, not including NULL values. 
SUM([1, 2, 2, 4]) = 9 SUM([Order Amount]) 
Analytical
Function  Description  Examples 

RUNNING_AVG(<expression>) 
Returns the running average of the given expression,from the first row in the partition to the current row. The given expression must be either an aggregate or a constant. 
RUNNING_AVG(SUM([Order Amount]) 
RUNNING_COUNT(<expression>) 
Returns the running count of the given aggregate expression, from the first row in the partition to the current row. The given expression must be either an aggregate or a constant.  RUNNING_COUNT(COUNT([Order Id]) 
RUNNING_SUM(<expression>) 
Returns the running sum of the given aggregate expression, from the first row in the partition to the current row. The given expression must be either an aggregate or a constant.  RUNNING_SUM(SUM([Order Amount]) 
WINDOW_AVG(<expression>, [<start>, <end>]) 
Returns the average of the given expression within the window. The window is defined by means of offsets from the current row. The given expression must be either an aggregate or a constant.<start> and <end> are optional integer parameters that define the partition. They are indices based on the current reference point If the start and end are omitted, the entire partition is used. FIRST()+n and LAST()n are can be used as offsets from the first or last row in the partition 

WINDOW_COUNT(<expression>, [<start>, <end>]) 
Returns the count of the given expression within the window. The window is defined by means of offsets from the current row. The given expression must be either an aggregate or a constant.<start> and <end> are optional integer parameters that define the partition. They are indices based on the current reference point (see picture below)


WINDOW_SUM(<expression>, [<start>, <end>]) 
Returns the sum of the given expression within the window. The window is defined by means of offsets from the current row. The given expression must be either an aggregate or a constant.<start> and <end> are optional integer parameters that define the partition. They are indices based on the current reference point (see picture below)

💡 For calculated field window functions, it’ll be helpful to understand how window partitions are defined.
SQL allows you to perform aggregations in different levels of the view using window functions, generally written as OVER (PARTITION BY column)
. Window functions also exist in calculated fields, though the way you define window partitions is different.
 Instead of specifying the partition directly in the formula code, you’d drag and drop the field into your chart axis along with your window calculated field. The system will automatically recalculate the values depending on your dimension.
 For moving windows, you’d specify a
<start>
and<end>
relative to the current row In general, n refers to the nth row before the current row, and n refers to the nth row after the current row.
 You can also crate offsets based on the first or last rows in the expression, using FIRST()+n and LAST()n.
FIRST()
always returns1
for the second row,2
for the third row, etc.LAST()
always returns1
for the secondtolast row,2
for the thirdtolast row, etc.
The corresponding formula for this window sum would be WINDOW_SUM(SUM([field]), 3, 2)
.
Calculated Field Component Types
Unlike your SQL results, which are always constants, calculated fields have different computation levels:
Order  Type  Description  Examples 

1  Constant  A fixed value.  1 TRUE 
2  Scalar  Values are mapped to a single result in a onetoone manner.  ABS() DATEDIFF() 
3  Aggregate  Values of multiple rows are grouped together as the input to form a single value of more significant meaning.  COUNT() SUM() 
4  Analytical  Computes aggregate values over a group of rows.  LOOKUP() RUNNING_SUM() 
Component Operations
You can combine various component types in operation.
Example:
1 + [column]
will add 1 to every row in your column. The result of that operation will take the greatest order of the combined data types —constant + scalar
returns ascalar
result.1 + SUM([column])
However, there are limitations to what calculated fields you can use in functions.
Nonexamples:
 Aggregating an aggregate 
SUM(COUNT([column]))
❌  Mixing aggregate and nonaggregate values in certain functions 
DATEDIFF('day', created_at, MAX(updated_at))
❌  Using scalar values in an analytical function 
RUNNING_COUNT([id])
❌
Last updated August 5, 2020