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 formula-based 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 multi-dimensional formula in Cube.
- Year-over-year 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 non-calculated 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 pre-defined "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.
Year-over-year visualizations
Visualizing year-over-year 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 decision-making.
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 (1-12).
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, percentile-value) | 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 non-null 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 (0-23). |
| 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 (0-59). |
| month | month([datetime column]) | Takes a datetime and returns the month number (1-12) 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 (1-4) 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 (0-59) 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 (1-7) 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. |