[From Wrike] Multi-Pass Aggregation in Wrike Analyze
Pinned Featured
Hi Community!
I am Katsiaryna, an Analytics Subject Matter Expert from Wrike Support.
Today I would like to share with you some ideas for aggregating data that can be used for different scenarios using Multi-pass aggregation.
What is it?
In simple words, multi-pass aggregation is an aggregation that performs multiple calculations simultaneously 👩🏫.
How does it work?
Multi-pass aggregation function aggregates the values at the specified level (project, task, timelog, etc.)
For example, we have multiple projects in a folder. Let's imagine that we want to see a sum of the allocated time (effort) from all the tasks in a project - that's the first level. And then we also want to see an average of those project-level results per folder. To run these calculations simultaneously, we can use multi-pass aggregation.
The first level of the formula will be quite simple: [Total Task Effort (minutes)].
Now we can add the average by Project ID to our formula: AVG([Project ID],[Total Task Effort (minutes)]) and divide it by 60 if we want to see the results in hours.
Another wonderful example of using multi-pass aggregation can be found in this post ✨.
Can it be used in other scenarios?
Sometimes, you may notice that a formula returns results that are much larger than expected, despite being based on a simple calculation. For example, a task custom field might show doubled or even larger values😥.
The complexity of the data model and the widget design, along with the account's folder structure, can lead to cases where a resulting value is multiplied due to the calculation of duplicates of the same value in some dimension. This happens more often with Timelog due to the granularity of the data table: each task may have several time entries.
Example: This issue occurs quite often with Timelog and the formula [Total Timelog Time Spent (minutes)]. Task "Flexible" is located under two subprojects of a target project "My project": "Duration" and "Ads copy". The basic SUM of Time Spent is likely to show 4 hours for the task, even though there are only 2 hours actually submitted. This is due to the duplication of the same entry across multiple parent locations.
Multi-pass aggregation is capable of ensuring the final result is calculated as expected😎. Here are the steps to achieve this:
- Determine the “lowest” ID level to use for aggregation (in the example above, Timelog ID).
- Remove the SUM function if it is used as the type of the numeric values and use MAX, AVERAGE, MEDIAN, or MIN instead.
Example: if you're using [Total Timelog Time Spent (minutes)], replace SUM with another aggregator:
- If the type is "All items", replace SUM with the aggregator.
Example: if instead of [Total Timelog Time Spent (minutes)] it was SUM(Timelog Time Spent (minutes)] used, replace it with MAX, AVERAGE, MEDIAN, or MIN. - Use SUM to aggregate on the ID level. Since every ID is unique, this prevents duplicates from being included in the calculation.
Example: (sum([Timelog ID], max([Timelog Time Spent (minutes)])))/60
I hope all of the above information will be of use and you will benefit from using the Multi-pass aggregation in your analytics boards! Please let us know if you have any questions in the comments below😊.
Katsiaryna S Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover
Katsiaryna S Wrike Team member Become a Wrike expert with Wrike Discover
Thank you for sharing this Katsiaryna S.
I will keep this in mind for the future.
I found very helpful your reference to Using DDIFF: On-Time Compliance SLA Tracking from Sam Alter, which is a very good improvement for my time adherence reports
Rohan V Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover
Rohan V Wrike Team member Become a Wrike expert with Wrike Discover