Using Formulas in Advanced Reporting Widgets
You can create and use formulas when adding a widget to your analytics board. To access the formula editor, click +Select data and then click the formula icon.
The formula editor has two tabs: the Data Browser to select fields and the Functions tab to select formula operations. You can create a formula combining one or more functions, fields, and filters.
Important
-
Functions are operations that perform different calculations, e.g., a sum. Use the Jump To menu or the search box to quickly find the formula you need.
-
Fields in the data browser are variables contained in the data set. Clicking a field in the data browser will include it as part of the formula.
-
Filters can be applied to restrict formulas based on criteria.
-
While editing an analytics board, click +Widget.
-
Click the +Custom widget button.
-
Click the plus button next to Values.
-
In the window that opens, click the fx button.
-
The data browser then changes to display the formula editor, which has two tabs: Data Browser and Functions.
-
Select a field from the Data Browser.
-
Switch to the Functions tab and select the required function.
-
Type in the required parts of the formula.
-
Click OK to save the formula and create a widget.
Often, formulas must take into account specific criteria. To do this Wrike provides a feature called Measured Value, which is similar to the SUMIF function in Excel, and performs a calculation only when the values meet a set of criteria.
Criteria for Measured Value may be based on any logical operators in a filter.
To filter the formula:
-
In the data browser, create your formula from Data Browser and Functions, as explained above.
-
Add the field (criteria) by which you want to filter the formula. Right-click the field and select Filter. You can filter the formula by listed items, text options, ranking, etc.
-
Click OK.
A common example of Measured Value is referencing a custom field, e.g., to sum a custom field for all projects, or to display it as a column in a Pivot table.
To reference a custom field, you can create a formula that sums custom field value with a filter over the custom field name.
To create a formula that sums or references a specific custom field:
-
Type ( in the formula editor.
-
Select Folder/Project Custom Field Numeric Value from the list below.
-
Type , in the formula editor.
-
Hover over the Folder/Project Custom Field Name field in the list.
-
Click More….
-
Select Filter and select the appropriate custom field from the list.
Note
For task custom fields, use Task Custom Field Name and Task Custom Field Numeric Value instead.
The final formula will look like:
([Total Folder/Project Custom Field Numeric Value], [Filter: Folder/Project Custom Field Name=’Budget’])