You make the best decisions when you can review all the important data at a glance. Advanced equations in custom worksheets bring information from several pages right into one view.
Before you start creating your own advanced equations, it is worth talking about the equation syntax and how to read it. Once you understand how the syntax works, you can build the exact equation you need to highlight trends or uncover insights in your data.
In this article, we will walk through advanced equations in custom worksheets step by step, from the simple to the sophisticated. You will learn how to read and write equations yourself and how to customize them for your needs.
To create your own advanced equations, you need to create a worksheet custom field. Navigate to Settings ⚙️→ Account → Custom fields. You will need to be an administrator with customizations permissions to do this. Click on the record type tab where you want to add the worksheet, then click Add custom field. Select Worksheet, then Next, and you are ready to create.
Click any of the following links to skip ahead:
Equation syntax overview
It might be helpful to think of your equation as a bridge you are building between an Aha! Roadmaps record and the field you want to reference.
You add an equation to a record, so every equation starts on that record, denoted by self.
Equations are broken into steps using periods .
Brackets [], also called arrays, will return all values for a field. You can filter the results with operators or functions.
Reference a field on a record
Let's start with a simple example and reference a feature's field from the feature itself:
self.due_date
Read left to right, this equation says "from the feature, find the Due date field and give me that date."
Reference a field on another record
Next, let's build that bridge from one record to a field of another linked record. Since you build these equations sequentially, you need to be able to describe each step between your first record (self) and the field you want to reference. For example, you could start with a feature and reference a field on the release that the feature is in, because you can describe each step in that process.
Let's take that first example and say that we want to reference a release's start date on a feature. That equation would look like this:
self.release.release_date
Read left to right, this equation says "from the feature, find the feature's release, then find the Release date field on that release and give me that date."
Remember, periods . break this equation into individual steps. We start with self.. Then we need to move to the feature's release with release.. We cannot skip ahead to release_date because then the equation would look for a field called "Release date" on the feature — and that is not what we are looking for.
Filter your equations with conditions and arrays
So far we have used example equations that have only one result: one feature Due date, and one Release date. But what if the field you want to reference has multiple results? For example, a feature could have multiple requirements. A custom table could have multiple rows. Or you could want to sum the votes of multiple ideas.
In these situations, if you build an equation using the process we have described so far, your equation will return all available results — all the requirements, all the custom table rows, and all the ideas' votes.
To filter those results, add conditions between the brackets [], which indicate an array of results.
Let's create an equation that will filter the results of an array.
self.requirements[].name
Read left to right, this equation says "from the feature, look at the feature's requirements and give me all of their names."
Arrays, denoted by brackets [], return all of a field's values. But we want to filter those results, so we need to add conditions to the array. To do that, we build out the filter in much the same way we build advanced equations:
self.status=="In design"
Read left to right, this filter says "on each requirement, look at its Status and track any requirement whose status is exactly In design."
The filter goes between the brackets [], so the self in this case references requirements. After referencing the requirement's Status field with self.status, we used two equals signs == to indicate that the status should be exactly In design. The == symbol is an example of a logical operator. You can filter arrays with logical operators or full functions, including IF, SUM, and COUNT.
Let's put it all together!
self.requirements[self.status=="In design"].name
Read left to right, this equation now says, "from the feature, look at the feature's requirements. On each requirement, look at its Status. For any requirement whose status is exactly In design, give me its name."
Now, a list of requirement's names might not be useful to you. In that case, you can add a function to the equation, such as COUNT, to count the unique requirements that are currently in design.
You cannot filter an array using a custom field that returns multiple values, such as predefined tag or tags fields.
Include custom fields in advanced equations
You use custom fields to capture information that is vital to your workflow or business model, so it makes sense to include custom fields in your advanced equations. There are two differences between standard and custom fields in advanced equations:
When you reference a custom field, you use its API key. When you create a custom field, you also create its API key, the unique identifier for the field.
When you reference a custom field, you add custom. to the equation just before the field's API key.
Currently, there are a few custom field types that you cannot use in advanced equations.
To find a field's API key, navigate to Settings ⚙️→ Account → Custom fields. You will need to be an administrator with customizations permissions to do this. Click to the appropriate record type tab, then search or scroll for your custom field. You can click on the custom field's Name to confirm that it is the one you intend to reference, or copy the API key from the API key column of the search results.
Note: Custom fields need to be added to a custom record layout before you can reference them in advanced equations. From the list of custom fields, you can see which layout(s) a field has been added to from the Used in layouts column. If you want to add a custom field to a custom layout, click the field's Name and add it to a layout using the Custom layouts dropdown. If you want to adjust the field's visibility settings or arrange it on the record, navigate to Settings ⚙️→ Account → Custom layouts and edit the appropriate layout. You will need to be an administrator with customizations permissions to do this
Use this API key when you build your equation. Remember, first you need to build a bridge between the record type where you are adding your equation and the field you want to reference. Let's create a simple equation to reference a Pageviews per session custom field we created and added to the feature custom layout.
self.custom.pageviews_per_session
Read left to right, this equation says "from a feature, find the Pageviews per session custom field and give me that number."
Add functions and operators
So far, we have created equations that reference some or all of a field's values. But of course equations can analyze data as well as display it. Think about what you want to do with the field values you want the equation to give you, and then choose the appropriate function or operator.
For example, let's create an equation that will give us the total cost of all features linked to an initiative.
sum(self.features[].custom.feature_cost)
Read left to right, this equation says "add together all of these values: start from the initiative, look at all the features linked to it, and on each feature look for the Feature cost custom field and add that number to all the others."
You can build much more robust functions into your advanced equations, and the Aha! Roadmaps equation builder will help you by telling you how many arguments it is expecting (meaning, whether you have left part of the equation out), whether the equation is valid, and providing a list of available fields, functions, and operators for you to choose from.
Create sophisticated equations
Ready to put it all together? With a little practice, you can become proficient at advanced equations, and start making connections between records and fields to highlight insights you would not be able to visualize otherwise.
To show the power of advanced equations, let's create an equation that references a custom table's records as they relate to the work in progress features linked to an initiative. Custom tables, available to Enterprise+ customers, are tools to help you expand the Aha! Roadmaps data model in a custom spreadsheet that you can reference across other records and reports.
count(self.features[self.status!="Not started"].custom.opportunities[])
Read left to right, this equation says "count the number of all these values: from an initiative, look at all the features linked to it that do not have the status Not started, and on each feature look for the Opportunities custom field, and add the number of opportunities to all the others."
In this case, the Opportunities custom field is a many-to-many custom table relationship field, and we have created a custom table to track customer opportunities. We then linked that table to the feature record type, so that we can track the work that would benefit each opportunity.
In this equation, we are counting all the opportunities across all the features linked to the initiative. If we added this equation to a custom worksheet field and displayed that field on the initiative, then anyone could see at a glance how the initiative supports our strategic opportunities.
Further reading
Ready to create your own advanced equations? Great! We have more articles to help you build, filter, and customize your equations: