All articles
Wrike Analyze: Formulas
Wrike Analyze is a paid add-on available to regular users on Business and higher accounts.
- Overview
- Using Formulas in Widgets
- Creating Formulas Based on Criteria and Conditions (Filters)
- Using Formulas to Reference Custom Fields
Overview
Formulas are custom calculations performed on one or more fields in your data. They offer an important way to analyze results and express business logic. Using formulas in your widgets helps you to build expressions on various fields from various objects in your data, for example:
- Count tasks, projects or other objects, total or under certain condition
- Sum timelogs, custom fields and other data
- Reference custom fields
- Calculate date or numeric field differences
- Calculate averages
- Perform complex business calculations
- Refer only to data matching specific criteria and conditions
All formulas in Wrike Analyze widgets must return numeric values. You can reference any non-numeric data, but the outcome will still be numeric. For example, you can calculate the difference between Project Start Date and Due Date (duration), but it will be expressed as a number, e.g. number of days.
The table below provides a reference to the main formula functions available in Wrike Analyze.
| Goal | Function | Types and Syntax |
| Perform calculation based on criteria | Measured value |
Value Filters: ≠ , =, >, <, between Text Filters: Contains, Doesn't Contain, Doesn't End With, Doesn't Start With, Ends With, Start With, Equals, Not Equal List Filter: Include, Exclude Ranking Filters: Top, Bottom Ranking Time Filter: Date and Calendar |
| Combine data/apply simple mathematics | Aggregate functions |
Operator: +,-,*,/ Aggregate: Sum() Average: Avg() Count: Count(), DupCount() Range: Max()/Min() |
| Summarize data | Statistical function |
Central Tendency: Median(), Model, Largest() Std Deviation and Variance: Stdev(), Stdevp(), Varp(), Var() Quartile and Percentile: Quartile(), Percentile() |
| Accumulate data | Rolling sum/average |
Sum to Date: YTDSum(), QTDSum, MTDSum() Avg to Date: YTDAvg, QTDAvg, MTDAvg() |
| Compare Time or Trends | Time functions |
Past Periods: PastYear(), PastQuarter(), PastMonth(), Next(), Prev() Growth Trend: Growth(), GrowthRate() Time Difference: YDiff(), QDiff(), MDiff(), DDiff(), HDiff(), MnDiff(), SDiff() |
Using Formulas in 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 function, field and filter(s).
- Functions are operations which perform different calculations, for example: 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 on a field in the data browser will include it as part of the formula.
- Filters can be applied to restrict formulas based on criteria.
To create a widget based on a formula:
- 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. You can see some examples of formula use here.
- Click “Ok” to save the formula and create a widget.
Creating Formulas Based on Criteria and Conditions (Filters)
Often formulas must take into account specific criteria. To do this Wrike Analyze 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 Values may be based on any logical operators in a filter.
To filter the formula:
- In the Data Browser, create your formula from the Data Browser and Functions, as explained in Using Formulas In Widgets.
- 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.
- When done, click OK.
Using Formulas to Reference Custom Fields
A common example of Measured Value is referencing a custom field, for example to sum a custom field for all projects, or to display it as column in a Pivot table.
To reference a custom field, you can create a formula that sums custom field value with a filter over custom field name.
To create formula that sums or references 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 “Folder/Project Custom Field Name” field in the list.
- Click “More…”.
- Select “Filter” and select 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 would look like:
([Total Folder/Project Custom Field Numeric Value], [Filter: Folder/Project Custom Field Name=’Budget’])
