Forecast Advanced Analytics comes with prebuilt fields that you can report on, through your widgets. From time to time you may want to report on data that is not already pre-built as a field in Advanced Analytics. Creating your own formulas gives you the flexibility necessary to present information or data that isn't included in Advanced Analytics, but is still available through the dataset.

This article covers:

## Creating and editing formulas

The Advanced Analytics Formula Editor enables you to create complex business calculations based on multiple data sources (e.g projects, time registrations), without the need to manipulate raw Forecast data. With custom Formulas you can:

- Apply various filters to fields (also known as values) and perform calculations based on the different criteria such as text, ranking and time filters.
- Use simple math to combine functions and data, including sums, aggregates, counts, and ranges.
- Condense your data using statistical functions that include standard deviation, variance, quartiles, and percentile functions.
- Accumulate the data using the current totals and averages for time periods and ranges.
- Compare time and trends with a time function such as changes over time, growth trends, and time differences.

To create/edit a custom formula

- Head to Advanced Analytics.
- Once you are in your dashboard, formulas can be created in two ways:
- In a new widget window, click
**Select Data**, and then the formula icon. - For an existing widget, click on the
**Edit Formula**button next to the 'Value'. - Within the Formula Editor it is possible to start type your formula and select pre-built fields and functions from the two tabs at the bottom of the pop-up:
**Data****Browser**: a full list of fields available in Advanced Analytics. See Overview of the Advanced Analytics Dataset for more information.**Functions**: provides all of the functions that can be used in your formula.

- Now that you have opened the Formula Editor, you can start composing your custom formula:
- Select the appropriate field from the
**Data Browser**tab. - Select the required functions from the
**Functions**tab. - Type in the required parts of the formula.

- Select the appropriate field from the
- Click
**OK**once the formula is ready.

- In a new widget window, click

## Overview of functions

Advanced Analytics provides with several functions to chose from to build your calculations, such as statistical, math, date&time and conditional statement functions. Below are a few examples that can be selected to build your formula.

#### Statistical functions

- Sum: Calculates the total of the given values.
- Average: Calculates the mean average of the given values.
- Minimum: Returns the minimum value among the given values.
- Maximum: Returns the maximum value among the given values.
- Count Unique: Counts the number of unique values within the given values.
- Count All: Returns the actual item count of the given list of items, including duplicates.

#### Conditional Statement functions

Conditional statements are formulas that allow you to create more categories in your data set. When the condition is met, the category you chose will be added to your visualization without modifying the data source.

The conditional statements available are:

- IF:
IF (Boolean_expression, true value, false value)

This will return the first value if it is true, or the second if it is false. - isNULL:
ISNULL (<numeric value>)

This will return true, if there is no data contained in the expression. - Case:
CASE

This function will evaluate boolean expressions. If the expression is valued true then the

WHEN Boolean_expression THEN result_expression

[ ... ]

[ ELSE else_result_expression ]

END*result_expression*will return. If there is no match, then the*else_result*expression returns. If there are no values then*Null*returns.

## Additional options

### Quick Functions

With Quick Functions you add contribution or time-dimension functions to any existing value or formula, such as calculations for changes over time, contribution and running average.

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

#### To apply a Quick Function

- Head to Advanced Analytics.
- In an existing widget, locate your formula under '
*Values*'. - Click on the
**three-dots icon**next to the formula. - Select
**Quick Functions**from the dropdown. - Select your function to automatically apply it.

### Changing calculation methods

It is possible to quickly update your formula with an aggregate function, if you are looking to use a different calculation method for your value (e.g as average or medians).

#### To change calculation methods

- Head to Advanced Analytics.
- In an existing widget, locate your formula under '
*Values*'. - Click on the value icon on the left of the field.
- Select the calculation method of choice.

## Comments

0 comments

Article is closed for comments.