Aha! Roadmaps | Filter arrays with logical operators
Custom worksheets help you quickly access and report on data found throughout the Aha! Roadmaps data model. Within these worksheets are calculations that can reference Aha! Roadmaps fields and perform calculations on them using functions and operators. Sometimes, the field that you are referencing is an array of values. In your equation, you can either return all of the values in the array or filter the array.
Click any of the following links to skip ahead:
Overview
Array filtering helps you customize the results of your equation. For example, you can define a custom worksheet equation for features that returns a count of completed and open requirements, as shown below. This helps you quickly see and report on how many requirements remain open for each feature.
You can also display arrays as results on your custom worksheet. For example, you could set up a releases custom worksheet that shows the names of all critical features that are not yet complete.
Example equation: self.features[self.type == āCriticalā AND self.status != āCompleteā].name
Result: Enhanced language options, Add stages for touring groups, Profile updates
You cannot filter an array using a custom field that returns multiple values, such as predefined tag or tags fields.
Array syntax
In this section, we will explain how you can use the equation builder to write equations that include arrays and filtered arrays. You can also read an introduction to advanced equations.
First, there are a few basic rules to understand about using arrays:
Arrays are identified in the equation builder with brackets. Click here to see a complete list of the field identifiers for advanced equations, including arrays.
When the result of your formula is an array, the results are returned in a comma-separated list. The results of your array are determined by the indicator which follows the brackets. In this example, we use the features[] array to return the reference IDs for each of the features in the array.
Example equation: self.features[].reference_num
Result: FRED-123, FRED-44, FRED-991
To filter the results of the array, you will need to indicate the desired results within the brackets of the array. Here are a few rules and examples to follow when filtering arrays:
Arrays can be filtered to a specific value by including a statement within the array brackets that includes a field (ex. reference.num), a logical operator ( ,<, =,<=,==,!=), and a record value (ex. āFRED-123ā). Record values must start and end with quotations. In this example, we will use the feature array, filter for a specific feature reference ID, and return the name of the feature.
Example equation: self.features[self.reference_num == "FRED-123"].name
Result: Establish a new partner portal
Arrays can be filtered with multiple statements, each containing a field, a logical operator, and a record value. These statements must be separated by OR, AND, or NOT. In this example, we will show all features that have a status of scheduled with a score that is higher than 12.
Example equation: self.features[self.status == "Ready to develop" AND self.score 12].name
Result: GPS point tracking, Share ride history on social media
Arrays can be combined with functions. Click here to see a complete list of functions. To combine an array with a function, include the array within the parenthesis of the function. In this example, we will show the sum of all estimates (in hours) on features that are in a status of āReady to developā or āIn Designā that have a score higher than 12. Note: The Estimate field returns a value in minutes. To present this in hours, we will divide by 60.
Example equation: sum(self.features[self.status == "Ready to develop" OR self.status == "In Design" AND self.score 12].original_estimate)/60
Result: 126
More examples
Example | Custom worksheet type | Equation | Result |
Show a count of all features with a type of āNewā included in this epic | Epics | count(self.epics[self.features.type == āNewā]) | 4 |
Show the sum of all feature scores contained in an epic | Epics | sum(self.features[self.score 5]) | 20 |
Show how many features in the current release have a completion date after a certain date | Releases / Schedules | count(self.features[self.due_date "2019-06-12"]) | 6 |
Show the cost of new features on an initiative based on a defined labor hour of $75 | Initiatives | sum(self.features[self.type == "New"].original_estimate)/60*75 | $31,000 |
Show the number of open requirements on a feature | Feature | count(self.requirements[self.status == "Under consideration" OR self.status == "In development"]) | 13 |