Using Formulas in Advanced Reporting Widgets
|
Availability: Pinnacle. ; Unavailability: Free, Team, Business, Enterprise; |
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 1.
-
Click the +Custom widget button 2.
-
Click the Add + button 3 next to Values.
-
In the window that opens, click the fx button 4.
-
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 5.
-
Switch to the Functions tab 6 and select the required function.
-
Type in the required parts of the formula.
-
Click OK 7 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. Hover over the field and click More 1 and select Filter 2 from the drop-down menu.
-
You can filter the formula by listed items, text options, ranking, etc.
-
Click OK 3.
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 1.
-
Select Folder/Project Custom Field Numeric Value from the list below 2.
-
Type , in the formula editor.
-
Hover over the Folder/Project Custom Field Name field in the list.
-
Click More… 3.
-
Select Filter 4 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’])
There is a vast variety of possible scenarios and ways of using formulas in analytics reports. Below you'll find examples of the most common use cases and helpful formulas that can be used when building your custom analytical reports. Click the corresponding link to learn more about your desired scenario.
-
Using a function ALL to ensure values are not limited by the specified dimension used to group the values [From Wrike] Using the ALL() function to build custom formulas – Wrike Help Center.
-
Using a function DDIFF to calculate a difference in days between two dates [From Wrike] Using the Days Difference (DDIFF) function – Wrike Help Center.
-
Combining DDIFF function with a conditional function IF to track on-time compliance SLA [From Wrike] Using DDIFF: On-Time Compliance SLA Tracking – Wrike Help Center.
-
Using functions and available analytical data points to track how many statuses are used, how long items spend in a status, etc. Workflow Analysis Basics.
-
Calculating project progress [From Wrike] Project Progress in Wrike Analyze – Wrike Help Center.
-
Using Multi-pass aggregation to avoid multiplications or combine different aggregations in one formula [From Wrike] Multi-Pass Aggregation in Wrike Analyze – Wrike Help Center.
-
Applying an advanced conditional function CASE on an example of actual cost calculations [From Wrike] Hardcore Analytics: Actual cost and fees – Wrike Help Center.
-
Using NOW to reference the current day [From Wrike] Using the NOW() function to reference today 📅 – Wrike Help Center.