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.
- Create your formula as explained above.
- Add the field (criteria) by which you want to filter the formula. Right-click the field and select Filter.
- 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 |
Time-Related Data
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:
|
|
Past Quarter |
PastQuarter([Numeric field]) |
Calculates the value for the same period in the past (previous) quarter. For example:
|
|
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:
Calculation: (12 – 10) / 10 = 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:
Calculation: 12 / 10 = 1.2
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