# Insights - Formula Guide

Updated 3 years 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.

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.2If 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.2If 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