[From Wrike] Hardcore Analytics: Actual cost and fees

Update: financial fields are now available in Wrike Analyze.

Hi Community,

My name is Herine, I support Wrike Analyze and deploy use cases, and today I’d like to share one of the most common ones: financial data in Wrike Analyze.

As you might already know, budgeting is not yet supported in advanced analytics (you can check which data is available here and here). However, it’s still possible to report on costs without transferring their values to custom fields.

These fields are Actual costs and Actual fees.

Both fields are calculated: they are based on timelog data (available in Wrike Analyze) and cost/bill rates (not available). In this post I’m going to describe how you can create a formula that would calculate Actual cost based on:

• Universal rate
• Custom cost rate specified in user settings
• Custom cost rate specified for a project.

Universal rate

The trick behind all 3 use cases is that rates need to be added to the formula manually, so the easiest use case is when everyone has the same rate.

Actual cost is Hours spent * Hourly rate. Let’s say the rate is 100, then the formula would look as simple as this

That’s it! I’m just also renaming the value “Actual cost”, so my pivot now looks like this

Custom user rate

While the use case described above looks simple and straightforward, usually rates vary between team members, therefore we’ll need to add conditions. If there are more than 2 conditions, the function CASE will help to define conditions by user ID. To do so, I’m adding Total Timelog User ID and filtering it by User Name.  

Here’s how:

1. after adding “Timelog User Name” to the formula, click it
2. select “Filter”
   
3. unselect everything and check the box next to the name of the relevant user
   

Ahsoka will be my first condition. I’m also renaming her filter so I don’t get confused when I add more patterns. The full condition will look like this:

Now I need to add an outcome. Let’s say her rate is 150

Done! Now I can simply copy then paste the entire WHEN - THEN pattern as many times as users I have, change rates, and change filters.

I have also added the pattern ELSE: it will use my universal 100 rate for all other users.

Note! Before saving the formula, it’s important to double-check that the filters are set to the correct names.

I’ve kept my first formula for comparison.

I’ve also added time spent / 60 as my last column (Hours Spent) so that my numbers are more understandable.

If I need reporting on projects, but by user rates, I can still use the same formula.

Custom project rate

Sometimes rates defined for projects are different from the rates defined in user settings, and they can be customized with the same logic as described above. My custom rates will be:

• Search: 250
• Stormtrooper Training: 150
• Tarkin Initiative: 300

My condition now will be filtered by Project Name:

And here’s the full formula (with renamed filters):

This is my favorite way of adding actual costs and fees to analytics reports! I hope it will be helpful for you too :)

P.S. - if your workflow is more complicated and you’re using different user rates for different projects, you can either:

• divide your report into several widgets, each one filtered by projects with the same rates, or by user names with the same rates; or

• play around with both formulas shared above to combine them for your use case; or

• request deployment for personalized assistance with finalizing your report (for this option, please, contact your Account Team or Customer Support).

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