Perform calculations on data in list reports
Aha! reports help you analyze your data — leading to more confident strategic decisions, more efficient workflow adjustments, and insights you might not have uncovered otherwise. Sometimes you need to move one step beyond the report you have created. To do this, use calculations.
Calculation columns make it possible to transform the data in your list reports using custom equations. You can create them on list reports, then present them on pivot tables, charts, and, for Aha! Roadmaps users, custom roadmaps. With calculation columns, you can get instant insights into your data to share with your team — no spreadsheets required.
Calculation columns are completely configurable for a range of uses, from simple operations to highly complex equations. Here are a few ways that you can leverage them:
Show progress on revenue goals by calculating the difference between revenue targets and actual revenue.
Determine if your features are on time for delivery by calculating the days until features are released using a combination of feature due date and the Today function.
Decide whether or not to pursue an idea based on a calculation of business value from your scorecard and the estimated cost to complete the work.
Click any of the following links to skip ahead:
Add a calculation column
To create a calculation column, first create a list report. Then at the far right of your list report, click the Plus + button and select Calculation column.
Next, you need to create a functioning equation. The Equation builder modal will open to help you through this.
Equations in calculation columns use functions and operators. You can even nest multiple functions and operators within each equation — for example, you can add an
IF statement as one of the arguments for another
IF statement, like this:
if(field("T-Shirt Size")=="Large", 3, if(field("T-Shirt Size")=="Medium",2,1))
Read left to right, this equation reads "if the record's T-Shirt Size field is Large, then return 3. If it isn't Large, return 2 if it's Medium and 1 if it's something else."
Equations work on any data element that appears in the same row — in other words, they work horizontally.
Let's walk through this step-by-step. To add a calculation column to a list report:
Create a new list report (or choose an existing list report).
For Aha! Ideas, navigate to Reports List.
For Aha! Roadmaps, navigate to Roadmaps List.
For Aha! Develop, navigate to Work List.
Click the plus icon that appears on the right side of the furthest column header shown on your list report. Then, choose Calculation column. The Equation builder will appear. Note: You can also add a calculation column by clicking the More details button, which appears when you hover over a column header.
From the Equation builder, you can begin to design your equation. Equations can contain the fields in your report and functions and/or operations. For a quick example, to determine if a feature has passed its due date, start by choosing the function for evaluating conditions: If.
When you are searching for a field, function, or operator, you can select a result by moving up or down with the arrow keys. You can add your selection using the Return/Enter key.
When you hover over a function, syntax and examples for the function appear on the viewing pane within the equation builder. For the purpose of this example, we will be starting with a function. However, you can also perform simple mathematical operations between fields by separating them with operators. For example, to combine two fields, you could enter an equation like this: field("Customer retention")+field("Sales increase")
Next, choose the fields that you would like to add, separated by a comma. The formula should look like this:
Feature due date
") < today(),"
If the equation does not include the proper syntax, an error will appear below the calculation row. If you save a calculation that does not contain the correct syntax, the column header will be red.
If you rename a column referenced in your equation, don't worry. It will automatically be renamed in the equation as well.
You can format calculation date columns with
Month DD, YYYYand
Mon DD, YYYYformats.
Click Save. The list report will now display the average of the two fields in the calculation column on your list report.
Customize and use calculation columns
You first create calculation columns in list reports, but they can be presented in chart, pivot, and, for Aha! Roadmaps users, custom roadmap reports. After you have added your calculation column, you can toggle between report types using the Change report type dropdown on the upper-left corner of the report.
Here are a few ways that you can further customize your report with calculation columns:
When you create a calculation column, a filter for that column is automatically added to your report as well. You can remove the filter by clicking the x next to it or add it back by clicking the + next to your calculation column. If you rename your calculation column, the filter's name will update when you next refresh the page.
Calculation columns can be sorted like any other data element in your list reports. You can sort a column individually by hovering over the column header, selecting the More details button, then choosing from one of the sorting options. Or, you can define a more complex sort order using the Sort data button in the top-left corner of your report.
To move a column, hover over the column header until you see the move cursor. Drag the column to the desired location.
Click on a column's name to rename it.
To edit the calculation or delete a calculation column, click the More details button that appears when you hover over the column header and then choose an action.
To define the number format of a calculation column, click the More details button on the column header, then choose # Column format to customize the output of your column.
The calculation column equation relies on the terminology of the workspace or team you have selected from the hierarchy in the upper left of your page. For example, if you have added the Feature name field to your report and build an equation with that field, then view your report from a workspace or team that uses another name for that record type, your equation will break. To resolve this, change the name of the field in your report by clicking on the column header and naming it something unique.
The equation builder will include every field in your report. If any field is incompatible with calculation columns, you will be unable to add it.
The following fields are not yet supported in calculations and will not appear in the equation builder:
Idea voter email domain
Predefined tags field