Insights - Formula Guide

Updated 1 year ago by Elena Yurkova

Insights - Formula Guide

Introduction The Formula Editor provides you with all the tools you need to create complex business calculations based on multiple data sources, without the need to manipulate raw data. This quick reference guide provides a quick overview of what you can do with the Formula Editor. For first-time users, we recommend the detailed guide and examples, in our online documentation.

What can you do with formulas? What Can Formulas Do for You?

  • Perform calculations based on criteria, by applying various filters on measures (what we call a measured value), including text, ranking, and time filters.
  • Combine data by applying simple mathematics with functions that include sum, aggregation, count, and range.
  • Summarize data using statistical functions that include standard deviation, variance, quartile and percentile functions.
  • Accumulate data with rolling sum and average for time periods and ranges.
  • Compare times and trends with time functions that include changes over time, growth trends and time differences.

How to Get Started

For a new widget, click Select Data, and then the formula button.

For an existing widget, click on the Edit Formula button.

The formula editor has two tabs:

  • Data Browser - Click on a field to include it in the formula.
  • Functions - Select formula operations.

You can create a formula combining one or more functions, fields, and filters.

How to reuse Formulas

You can save your frequently used formulas. Click on the star and then enter a name.

To reuse a formula, select it from under the Formulas in the Data Browser tab.

Build-in Functions

You can easily apply functions to your formulas without opening the formula editor, by using shortcuts. There are two methods, depending on the type of formula you need.

Quick Functions

Use Quick Functions to add contribution or time-dimension functions to any existing value or formula. They include calculations for past values, changes over time, contribution and running totals. Quick Functions can only be accessed by clicking on a formula that is already present in a widget.

Click on the menu icon of a numeric field in the data pane, and select Quick Functions and the function that you want to apply.

Aggregate Functions

To quickly update your formula with an aggregate function, click on the value icon, and select a different calculation method.

Create your Formulas

See the different ways to use formulas.

Filter Formulas Based on Criteria

Use Measured Value to perform a calculation based on values that meet specific criteria.

  1. Create your formula as explained above.
  2. Add the field (criteria) by which you want to filter the formula. Right-click the field and select Filter.
  3. You can then filter the formula by listed items, text options, ranking, etc.
Combine Data

Use the following aggregate functions to combine data:

Sum

SUM(Numeric field)

Calculates the total of the given values

Average

AVG(Numeric field)

Calculates the mean average of the given values

Minimum

MIN(Numeric field)

Returns the minimum value among the given values

Maximum

MAX(Numeric field)

Returns the maximum value among the given values

Count Unique

COUNT(Numeric field)

Counts the number of unique values within the given values

Count All

DupCount(Numeric field)

Returns the actual item count of the given list of items, including duplicates

Use the following functions to calculate time data and ranges:

Prev

PREV([Date field],[N])

Returns the Time period Member in [Date field] which is N periods back from the current Member. This function only works as a scope function and not by itself. For example – This formula will return opportunity revenue of 2 months ago: ([Total Revenue],PREV([Month Close Date],2)

Next

Next([Date field],[N])

Returns the Time period Member in [Date field] which is N periods after the current Member. This function only works as a scope function and not by itself. For example – This formula will return opportunity revenue expected for next 2 months: ([Total Revenue],NEXT([Month Close Date],2)

Past Year

PastYear([Numeric field])

Calculates the value for the same period in the past (previous) year. For example:

  • If you’re looking at a specific day, you will see the value of the same day one year back.
  • If you’re looking at a specific month, you will see the value of the same month one year back

Past Quarter

PastQuarter([Numeric field])

Calculates the value for the same period in the past (previous) quarter. For example:

  • If you’re looking at a specific day, you will see the value of the same day one quarter back.
  • If you’re looking at a specific month, you will see the value of the same month one quarter back.

Past Month

PastQuarter([Numeric field])

Same as above, but for a month

Growth

GROWTH([Numeric field])

Calculates growth over time. The time dimension to be used is determined by the time resolution in the widget/dashboard. For example:

  • If this month your value is 12, and last month it was 10, your Growth for this month is 20% (0.2).

Calculation: (12 – 10) / 10 = 0.2

  • If this year your value is 80, and last year it was 100, your Growth for this year is -20% ( -0.2).

Calculation: (80 – 100) / 100 = -0.2

Growth Rate

GrowthRate([Numeric field])

Calculates growth rate over time. The time dimension to be used is determined by the time resolution in the widget/dashboard. For example:

  • If this month your value is 12, and last month it was 10, your Growth Rate for this month is 12/10 = 120% (1.2).

Calculation: 12 / 10 = 1.2

  • If this year your value is 80, and last year it was 100, your Growth for this year is 80/100 = 80% ( 0.8).

Calculation: 80 / 100 = 0.8

Year Difference

YDIFF([Start Date],[End Date])

Returns the difference between [Start Date] and [End Date] in years. Returns whole numbers.

Quarter Difference

QDIFF([Start Date],[End Date])

Returns the difference between [Start Date] and [End Date] in quarters. Returns whole numbers.

Month Difference

MDIFF([Start Date],[End Date])

Returns the difference between [Start Date] and [End Date] in months. Returns whole numbers.

Day Difference

DDIFF([Start Date],[End Date])

Returns the difference between [Start Date] and [End Date] in days. Returns whole numbers.

Hour Difference

HDIFF([Start Date],[End Date])

Returns the difference between [Start Date] and [End Date] in hours. Returns whole numbers.

Work Out Running Totals and Averages

Use the following functions to calculate running totals and averages.

Year to Date Sum

YTDSum([Numeric field])

Returns the running total starting from the beginning of the year up to the current time period member. The time dimension to be used is determined by the time resolution in the widget/dashboard

Quarter to Date Sum

QTDSum([Numeric field])

Returns the running total starting from the beginning of the quarter up to the current time period member. The time dimension to be used is determined by the time resolution in the widget/dashboard. Returns 0 if the active time resolution is years.

Month to Date Sum

MTDSum([Numeric field])

Returns the running total starting from the beginning of the month up to the current time period member. The time dimension to be used is determined by the time resolution in the widget/dashboard. Returns 0 if the active time resolution is quarters or years.

Year to Date Average

YTDAvg([Numeric field])

Returns the running average starting from the beginning of the year up to the current time period member. The time dimension to be used is determined by the time resolution in the widget/dashboard.

Quarter to Date Average

YTDAvg([Numeric field])

Returns the running average starting from the beginning of the quarter up to the current time period member. The time dimension to be used is determined by the time resolution in the widget/dashboard. Returns 0 if the active time resolution is years.

Month to Date Average

MTDAvg([Numeric field])

Returns the running average starting from the beginning of the month up to the current time period member. The time dimension to be used is determined by the time resolution in the widget/dashboard. Returns 0 if the active time resolution is quarters or years

For advanced functions see Insights - Conditional Statements in formulas


How did we do?