Custom expressions provide a powerful way to create formulas and visualize data within questions. They can be particularly useful in three scenarios:
 Mitigation: When you encounter limitations with formulabased questions due to rollup logic, custom expressions offer an alternative method to plot essential metrics based on Cube data.
 Convenience: Custom expressions can be a convenient solution if you want to visualize a combination of values that don’t warrant creating a full multidimensional formula in Cube.
 Yearoveryear comparisons: Plot multiple years in a single visualization to track performance over time using a custom expression to summarize each year.
Before building custom expressions, we recommend reading about how dashboard filters work with your data hierarchy. If the dimensions you plan to use in your custom expressions fall into different levels of your data hierarchy, remember to filter to the highest level to ensure all data is accessible in your custom expression.
The basics of custom expressions
Dashboards organize your data into a table, and you'll reference the column headers to perform calculations. The preview function will be useful to ensure your expression exactly matches column headers and dimension names.
I can see exactly what I'll need to reference [Value]
and [Account 3]
using the preview example below.
Custom expressions use the common math symbols for addition (+), subtraction (), multiplication (*), and division (/). There are also conditional operators available to help aggregate your data. These are AND, OR, NOT, >, >=, <, <=, and != (not equal to). There are additional expressions and functions available for more complex custom expressions. See the full list below.
You can use multiple custom expressions in the summarize and group by fields. You can use multiple expressions to create multiple series in your visualizations.
However, you cannot reference another custom expression from the same field it was created. If you need to reference a custom expression, create a new custom column and add the formula. When creating custom columns, you can reference custom expressions created in the summarize by field.
Recreate a Cube formula with custom expressions
You can always create formulas while building questions using custom expressions. Some formulas you already have in Cube may need to be recreated in dashboard questions using custom expressions due to their rollup logic.
Calculate leaf values, then sum  Locate the dimension in your question filters and use it directly in your question. 
Sum leaf values, then calculate  Recreate it as a custom expression in the summarize by field. 
Learn more about what considerations need to be made for formulas and tags when building dashboards.
Let's consider the scenario where we want to plot the Gross Margin in our question. The logical computation for Gross Margin is:
Gross Margin = Revenue  Cost of Goods Sold
Suppose we have Revenue and Cost of Goods Sold values stored in Cube but no predefined formula dimension already performing the subtraction logic.
To set up the question, we begin with Filters, configuring the filters to pull data rows where Account 2 equals Revenue OR Cost of Goods Sold.
 Account > Income Statement > Revenue
 Account > Income Statement > Cost of Goods Sold
Additionally, we add filters to focus on the actual scenario and only include noncalculated values to avoid double counting from related formulas or tags.
Click the preview arrow to the right of the filter selector to ensure the data coming back with our filter looks right.
To handle the mix of rows with Revenue and Cost of Goods Sold values and perform further calculations, we need to create a custom expression within the summarize logic for the Gross Margin formula and visualize it effectively.
This approach allows us to achieve the desired results even without a predefined "Gross Margin" formula in the Cube, and we can compare the outcomes to validate the correctness while experimenting with the data.
Click the plus button in the Summarize field, but instead of selecting the standard Sum of... option, choose Custom Expression, located at the bottom of the options.
From this point, you can create a formula that selects values from data rows where Account 2 equals "Revenue" and subtracts values from data rows where Account 2 equals "Cost of Goods Sold."
We’ll use the SumIf function for this:
SumIf([Value], [Account 2] = "Revenue")  SumIf([Value], [Account 2] = "Cost of Goods Sold")
And we’ll give the expression an appropriate name, in this case, “Gross Margin,” and click Done.
Finally, to modify how we want the values to be grouped for the formula's calculation, we'll choose Date: Month. This selection means the data will be plotted based on the monthly grouping.
When you press Visualize, the results should closely resemble what you would obtain by having a Gross Margin formula in Cube. It's as if you fetched the data into a spreadsheet and created a chart based on that formula.
Custom expressions are useful when you don't want the formula directly in the Cube. They come in handy when the formula involves rollup logic, and the final values are unavailable for visualization.
Yearoveryear visualizations
Visualizing yearoveryear data on your dashboard offers invaluable insights into your business's growth and trends. By comparing the same periods across different years, you can easily spot patterns, understand seasonal impacts, and gauge the effectiveness of your business strategies. This longitudinal view helps identify areas of strength and opportunities for improvement, facilitating more informed decisionmaking.
Use two or more custom expressions in the summarize by section of your question to define each year, month, or time period you want to visualize. These custom expressions will use the date functions to determine which data to include in each series. Months are tracked using their numerical equivalent (112).
Static time series custom expressions allow you to reference a particular month or year:
SumIf([Value], year([Date]) = 2023)
SumIf([Value], month([Date]) = 1)
Dynamic time series custom expressions will update referenced periods as time passes:
SumIf([Value], year([Date]) = year(now))
SumIf([Value], month([Date]) = month(now)  1)
Each custom expression added to the summarize by section will appear in the visualization settings as a new series so they can be customized.
Available expressions
The following expressions are available for use in the Summarize field.
Expression  Syntax  Description 
Average  Average(column)  Returns the average values for the column. 
Count  Count  Returns the number of rows or records with values in the column 
CountIf  CountIf(value)  Returns the number of rows or records where the condition is true. 
Cumulative Count  CumulativeCount  The additive total of rows across a breakout. 
Cumulative Sum  CumulativeSum(column)  The rolling sum of a column across a breakout. 
Distinct  Distinct(column)  Number of district values in a column. 
Max  Max(column)  Returns the largest value in a column. 
Median  Median(column)  Returns the median value of a column. 
Min  Min(column)  Returns the smallest value in a column. 
Percentile  Percentile(column, percentilevalue)  Returns the value of the column at the percentile value. 
Share  Share(condition)  Returns the percent of rows in the data that match the condition, as a decimal. 
Standard Deviation  StandardDeviation(column)  Calculates the standard deviation of the column, which is a measure of the variation in a set of values. Low standard deviation indicates values cluster around the mean, whereas a high standard deviation means the values are spread out over a wide range. 
Sum  Sum(column)  Adds up all the values of the column. 
SumIf  SumIf(column, condition)  Sums up the specified column only for rows where the condition is true. 
Variance  Variance(column)  Returns the numeric variance for a given column. 
The expressions below are the available functions that apply to each individual value. They can alter or filter values in a column or create new, custom columns.
Function  Syntax  Description 
abs  abs(column)  Returns the absolute (positive) value of the specified column. 
between  between(column, start, end)  Checks a date or number column’s values to see if they’re within the specified range. 
case  case(condition, output, …)  Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met. 
ceil  ceil(column)  Rounds a decimal up (ceil as in ceiling). 
coalesce  coalesce(value1, value2, …)  Looks at the values in each argument in order and returns the first nonnull value for each row. 
concat  concat(value1, value2, …)  Combine two or more strings. 
contains  contains(string1, string2)  Checks to see if string 1 contains string 2 within it. 
convertTimezone  convertTimezone(column, target, source)  Shifts a date or timestamp value into a specified time zone. 
datetimeAdd  datetimeAdd(column, amount, unit)  Adds some unit of time to a date or timestamp value. 
datetimeDiff  datetimeDiff(datetime1, datetime2, unit)  Returns the difference between two datetimes in some unit of time. 
datetimeSubtract  datetimeSubtract(column, amount, unit)  Subtracts some unit of time from a date or timestamp value. 
day  day([datetime column])  Takes a datetime and returns the day of the month as an integer. 
endsWith  endsWith(text, comparison)  Returns true if the end of the text matches the comparison text. 
exp  exp(column)  Returns Euler’s number, e, raised to the power of the supplied number. 
floor  floor(column)  Rounds a decimal number down. 
hour  hour([datetime column])  Takes a datetime and returns the hour as an integer (023). 
interval  interval(column, number, text)  Checks a date column’s values to see if they’re within the relative range. 
isEmpty  isEmpty(column)  Returns true if the column is empty. 
isNull  isNull(column)  Returns true if the column is null. 
leftTrim  lt(text)  Removes leading whitespace from a string of text. 
length  length(text)  Returns the number of characters in text. 
log  log(column)  Returns the base 10 log of the number. 
lower  lower(text)  Returns the string of text in all lower case. 
minute  minute([datetime column])  Takes a datetime and returns the minute as an integer (059). 
month  month([datetime column])  Takes a datetime and returns the month number (112) as an integer. 
now  now  Returns the current date and time. 
power  power(column, exponent)  Raises a number to the power of the exponent value. 
quarter  quarter([datetime column])  Takes a datetime and returns the number of the quarter in a year (14) as an integer. 
regexextract  regexextract(text, regular_expression)  Extracts matching substrings according to a regular expression. 
replace  replace(text, find, replace)  Replaces all occurrences of a search text in the input text with the replacement text. 
right trim  rtrim(text)  Removes trailing whitespace from a string of text. 
round  round(column)  Rounds a decimal number either up or down to the nearest integer value. 
second  second([datetime column)  Takes a datetime and returns the number of seconds in the minute (059) as an integer. 
sqrt  sqrt(column)  Returns the square root of a value. 
startsWith  startsWith(text, comparison)  Returns true if the beginning of the text matches the comparison text. 
substring  substring(text, position, length)  Returns a portion of the supplied text, specified by a starting position and a length. 
trim  trim(text)  Removes leading and trailing whitespace from a string of text. 
upper  upper(text)  Returns the text in all upper case. 
week  week(column, mode) 
Takes a datetime and returns the week as an integer. mode: Optional.

weekday  weekday(column)  Takes a datetime and returns an integer (17) with the number of the day of the week. Sunday = 1 
year  year([datetime column])  Takes a datetime and returns the year as an integer. 