[From Wrike] Fantastic Fields and Where to Find Them: Calculated Custom Fields in Wrike Analyze

Update: Calculated Custom Field are now available in Wrike Analyze.

Hi Community,

I’m Herine, Wrike Analyze Subject Matter Expert from Support 👋🏻  In my four-post series, I’ll be sharing some tips and tricks on how I use custom fields in Wrike Analyze, covering limitations and technical details. I’d like to begin with calculated custom fields in Wrike Analyze, and I’ll discuss the usage of several fields in the same widget in my next posts.

You might find this piece helpful if:

• You have calculated custom fields in Wrike and need to report on them in analytics boards.
• You want to learn how filters are used within formulas.
• You’d like to create custom metrics based on several custom fields.

Calculated custom fields: Theory

There are two types of formula fields in the native Wrike functionality: calculations on reports (native Reports, not analytics reports), and calculated custom fields in Table view (also accessible from some other views). Both features are tied to their original source and are not exported into Wrike Analyze. 

However, the good news is that Wrike Analyze can analyze almost everything! There are no formulas in calculated fields that cannot be recreated in Wrike Analyze.

To replicate them in analytics reports, filters need to be added to formulas. A filtered pattern in formulas consists of the main calculation and the filter hugged by parentheses. 

Now - let’s look at some examples! Here is one from my test account:

The fields “resource units” and “missing units” are standard numeric fields, and “calculation” shows their difference. Let’s now check how this difference can be replicated in Analyze.

Using filters in formulas

When more than one custom field is needed in a formula, custom field filters should be placed inside the formula too (not widget filters). I’ll need two dimensions for that: 

• “Task Custom Field Numeric Value” – the main calculation,
• “Task Custom Field Name” – the filter.

Follow these steps to create a formula that will show values from a specific numeric custom field:

• Click on the “Add +” button under “Values”.
• Search Data Browser by “num”.
• Select “Task Custom Field Numeric Value”:
  

         Wrike Analyze will automatically transform it into a formula.

Note: it’s important to make sure that the dimension from the correct level is used. My widget is based on tasks, so I should remember to use “Task Custom Field Numeric Value” and not “Folder/Project Custom Field Numeric Value”.

• Click on the pencil icon on the top-right of the value.
• There’s already a formula with the function SUM. Add an opening parenthesis before it.
• At the end of the formula, add “Task Custom Field Name”, separated from the main calculation by a comma.
• Close parentheses:
  
• Click “Task Custom Field Name” in the formula.
• Select “Filter”:
  
• Choose the field name.
• Click “OK”:
  

Finalizing the formula from a calculated custom field

Now my formula is showing just the values from the custom field “resource units”. Since I need “missing units” too, I’m repeating the same steps. Finally, I just need to extract my “missing units” block from the “resource units” block:

I just copied and pasted it, and then changed the filter by clicking “Edit filter”:

To make it more readable, I’m also renaming my filters:

P.S. - It’s not necessary to break formulas by spaces or rows, but when a formula is too long, it’s easier to revise it when each pattern is separated.

That’s it! Now I have a formula that replicates my calculated custom field.

I hope this was helpful for you!

As a quick recap, here’s what you need to remember about calculated custom fields and analytics reports:

• Calculated custom fields are not available in Wrike Analyze, but you can replicate them with analytics functions.
• Custom field filters in formulas are friendly to each other: you can create formulas based on multiple custom fields.

Enjoy Wriking formulas! 😉

Herine Community Team at Wrike Wrike Product Manager Learn about Wrike’s killer features and best practices with our Online Training Webinars

Herine Wrike Team member Learn about Wrike’s killer features and best practices with our Online Training Webinars