[From Wrike] Using the ALL() function to build custom formulas
Hi Community,
My name is Mobeen, and I am back to talk about another function that could help you build more advanced formulas, the ALL() function.
What is ALL () and what does it do?
ALL () helps you override any filters that you may have on the dashboard or the widget level. It can be useful in cases where you want to do calculations involving a formula that requires a calculation between a filtered value and a total value (unfiltered). It can also help ignore the scope of your columns in a pivot table. You can find more details and its syntax in the functions tab.
Let’s take a sample use case to see ALL () in action.
I would like to calculate how many tasks each user has as a percentage of my total tasks. The first step is to count the total tasks each user has. We can do so by adding the column for “Task Assignee” and counting the tasks for each. I will use a pivot table for this example. Here is what it would look like:
My value for counting tasks is simply: COUNT(Task ID).
To calculate the percentage of tasks each user has compared to the total tasks assigned to the whole team I need to divide the number of tasks assigned to each assignee by the total number of tasks. That requires me to remove the pivot scope of the assignee column in my denominator. I will update the formula to the following:
In the denominator, I used the formula filter to allow ALL task assignees when counting the tasks, while the numerator still counts the tasks for each assignee as the pivot table column for task assignee divides the rows accordingly. I can change it to be shown as a percentage from the data type option in the settings for the value and my final output would look like the following:
This is a simple yet effective way to do calculations where a part of the formula requires an unfiltered value.
Here are some more examples where you may need to use the ALL () function:
· Calculating time spent by a user in comparison to the total time spent on a project
· Effort allocated to a user as a percentage compared to the total effort for the whole team
· Completed tasks by a user in comparison to the whole team
· Percentage cost across one category compared to the total cost across categories
Let us know below if this was helpful for you and any more use cases that you can think of!
Mobeen Tahir Community Team at Wrike Wrike Product Manager Узнайте о самых популярных функциях Wrike и советах по его использованию
Mobeen Tahir Wrike Team member Узнайте о самых популярных функциях Wrike и советах по его использованию
Where do we use this formula? In a new custom field?
Hi Sarah Dungey, this formula can be used in any of the examples mentioned above. It can also be used in a custom field. If you have a use case where you'd need to use ALL () to override any custom field filters and include all values, this could be perfect. Let me know if this helps, or if you want to discuss a particular use case feel free to reach out.
Mobeen Tahir Community Team at Wrike Wrike Product Manager Узнайте о самых популярных функциях Wrike и советах по его использованию
Mobeen Tahir Wrike Team member Узнайте о самых популярных функциях Wrike и советах по его использованию
Hi Wrike Team,
I need to create a simple Numeric Custom Widget for projects with assigned Owners. The widget filter was set to the Project Owner (excluding empty owner fields). I used the following formula with, Could you please help me get the percentage?
Formula:
count([Project ID]) / count([Project ID], all([Project Owner]))Thanks!
Hi Nishanth Joseph,
Herine from Wrike is here!
Are you building an indicator widget? If so, I think instead of using the ALL function and a widget filter, you can use a formula filter:
(COUN([Project ID]),[Project Owner]) / COUNT([Project ID])
Where [Project Owner] is set to exclude N/A:
With this setup, you don't need a widget filter by Project Owner.
I hope this helps!
Herine Community Team at Wrike Wrike Product Manager Узнайте о самых популярных функциях Wrike и советах по его использованию
Herine Wrike Team member Узнайте о самых популярных функциях Wrike и советах по его использованию