Filtering arrays with logical operators

Custom worksheets help you quickly access and report on data found throughout the Aha! data model. Within these worksheets are calculations that can reference Aha! 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.

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.

aha-advanced-equation-worksheets-features-requirements.png

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

Array syntax

In this section, we will explain how you can use the equation builder to write equations that include arrays and filtered arrays.

First, there are a few basic rules to understand about using arrays:

  • Arrays are identified in the equation builder with brackets. To see a complete list of the field identifiers for advanced equations, including arrays, see our complete list.
  • 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. See our complete list of functions here. 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 master feature

Master features / Master activities

count(self.epics[self.features.type == “New”])

4

Show the sum of all feature scores contained in a master feature / master activity

Master features / Master activities

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


Was this article helpful?
0 out of 0 found this helpful