[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:
- after adding “Timelog User Name” to the formula, click it
- select “Filter”
- 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 En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online
Herine Wrike Team member En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online
Thanks Herine. This is quite helpful, and I enjoyed your Star Wars characterisation. 🤓
In my opinion though this workaround indicates how far short Wrike Analyze currently is from being a professionally acceptable tool.
I will probably wind up using something like the approach that you've described, so thanks for taking the time. However I will be very uncomfortable that key financial metrics will be reported based on a very manual and therefore inherently error-prone workaround.
Do you have any info on when budgeting will be properly supported?
Hi Mark Tucknutt, hope you don't mind me jumping in for Herine. Thank you for taking the time to share your candid feedback here, we do appreciate it. I'll pass it on to our Product team and if there are any updates in relation to the product you can always find them under our Weekly Release Notes.
I agree with Mark Tucknutt, and I am really grateful for the workaround Herine has provided. It definitely is helpful for building a deeper understanding of the functionality within Wrike that might not be so obvious at first glance.
However, this workaround does only give me one piece of a multi-dimensional analysis I'm trying to achieve with data from the Wrike ecosystem. Having so many manual interventions required to model these analytics hobbles my ability to automate and scale.
For now, I am forced to work from spreadsheets to build charts with data from Wrike (which is also very subject to human error).
Keep up the good work, hopefully in time more and more of these fields will be available natively in Wrike Analyze.
Hi Eric Granger, welcome to the Community, and thank you for providing us with your use case and feedback. I've shared them with the Product team and we'll be sure to let you know of any updates🙋🏻♀️
HI
Please help!
Can you see where I'm going wrong. & why I'm getting this error message?
Hi Herine
I would greatly appreciate if you could help with my attempt of using the Case function
We have timelog categories of billable & non billable. Originally I used an IF statement as there were only two conditiones.
The issues i have now is that we have three timelog categories (two different billable rates & non billable). I tried to use the Case/when as above but I get an error message.
Can you please help with what I'm doing wrong?
Thanks
Hi Greg Gibbs, I asked our support team and experts to reach out to you about your question, and I see that you're in touch already. Please let me know if I can help with anything else!
Dear followers, I'd like to let you know that financial fields and capacity are now officially part of Wrike Analyze! You can find more info in this post: https://help.wrike.com/hc/en-us/community/posts/14598403615127--From-Wrike-Financial-Fields-and-Resource-Management-in-Advanced-Analytics-Wrike-Analyze-.
You can still rely on this guideline as an example of how the function CASE can be used, but there's no need in using it to reflect actual fees and costs anymore:)
Herine Community Team at Wrike Wrike Product Manager En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online
Herine Wrike Team member En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online
Is it possible to apply a custom rate to specific users and time periods. For example: EmployeeA has a rate of 10.00 for January of 2024. EmployeeA has a rate of 12.00 for February of 2024.
It seems like once you add a cost rate to someone on their settings, it changes everything before it.
I would like to define the time periods the rate applies to, so I was trying to use a formula for my Analytics Board report like you have above. I get an error "The Formulas used by this widget are not supported by this Live data source. Change the data source or change the formulas. "
Hi Cathy Snyder! User settings indeed do not allow defining time for a period. Once you change the rate of a user, all the time they track since that moment or the effort that is allocated afterwards will use those rates. For example, if the rate for October was 10, and users have already tracked everything for October, you can go ahead and change it to 12, and then create tasks and projects for November. Fees and costs will not be recalculated.
However, if you want costs and fees to be recalculated, you need to change the rates in the projects. You can create billing projects and assign project-specific rates instead. For example, instead of changing the rate of the EmployeeA in their settings, you can assign 12.00 for the project that was scheduled for February.
As for the analytics board, as long as it's based on Wrike Core, you can use the system fields instead of recreating the formula. You could use the formula I shared above, but if your rate will also depend on the date of the time entry (Timelog Creation Date), it would mean extra conditions in the formula too.
Herine Community Team at Wrike Wrike Product Manager En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online
Herine Wrike Team member En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online