Use Multi-Pass Aggregation in Analytics Boards

TL;DR

Use multi-pass aggregation to calculate data across levels and avoid duplicate totals.

Examples:

• Average by project: AVG([Project ID],SUM([Task Effort (minutes)]))/60
• Deduplicate timelogs: SUM([Timelog ID],MAX([Timelog Time Spent (minutes)]))/60

• Overview
• Calculate Values at Two Levels
  • Example: Average Project Effort by Folder
• Fix Duplicated Values
  • Example: Duplicated Timelog Totals
• What’s Next?

Overview

Use multi-pass aggregation when you want one formula to calculate values at more than one level, like task, project, or timelog. It can also help you avoid inflated totals when the same value appears more than once in your data.

Calculate Values at Two Levels

Multi-pass aggregation lets you aggregate values at a specific level, then apply another calculation to those results.

Example: Average Project Effort by Folder

Let’s say a folder contains several projects. You want to:

• calculate total task effort for each project, and
• calculate the average of those project totals for the folder

The first-level value is:

SUM([Task Effort (minutes)])

To average that value by project, use:

AVG([Project ID],SUM([Task Effort (minutes)])) / 60

This formula:

• calculates total task effort for each project,
• averages those project totals, and
• converts minutes to hours

Fix Duplicated Values

Sometimes a simple formula returns a number that’s much larger than expected. This can happen when the same value is counted more than once because of your folder structure, widget setup, or data level.

This issue appears often in timelog data because one task can have several time entries.

Example: Duplicated Timelog Totals

A task called “Flexible” sits in two subprojects, under the same parent project. A basic sum of time spent can show 4 hours, even though only 2 hours were logged.

Use multi-pass aggregation to count each unique entry once.

1. Identify the lowest unique level for the data. For timelog data, use [Timelog ID].

2. Use a non-additive aggregator for the value. Replace SUM with MAX, AVG, MEDIAN, or MIN.

   

3. Sum the result by the unique ID level.

Use this formula:

SUM([Timelog ID], MAX([Timelog Time Spent (minutes)]))/60

This formula sums each unique timelog entry once, then converts minutes to hours.

What’s Next?

• Creating Custom Analytics Boards
• Build widgets in analytics boards