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 firsttime 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.
Buildin 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 timedimension 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. Rightclick 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 
TimeRelated 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