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 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 to 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.
- Click OK once the formula is ready.
- In a new widget window, click Select Data, and then the formula icon.
Overview of functions
Advanced Analytics provides several functions to choose 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 result_expression will return. If there is no match, then the else_result expression returns. If there are no values then Null returns.
WHEN Boolean_expression THEN result_expression
[ ... ]
[ ELSE else_result_expression ]
END
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.
Additional resources
Comments
0 comments
Article is closed for comments.