This article discusses functionality that is included in the Enterprise+ plan. Please contact us if you would like a live demo or would like to try using it in your account.

Aha! Roadmaps | Reference Aha! Roadmaps fields in custom worksheet equations (Enterprise+)

A custom worksheet is a type of custom field that helps you gain powerful insights in the form of a two-column spreadsheet that appears on your Aha! Roadmaps records. You can create as many rows as you need and configure them to automatically calculate data based on custom equations.

You can add worksheet fields to your equations by selecting the field in the equation builder. But you may want your calculations to reference data that exists elsewhere in Aha! Roadmaps — like a feature’s due date or original estimate, a release’s total capacity, or even a custom table in your account. In this support article, we will walk you through how to reference Aha! Roadmaps data in worksheet equations to help you answer questions like:

  • What is the total cost of this initiative based on the cost to develop its features?

  • How much of the workload in this release contributed to new features?

  • Using a variety of measurements like idea votes and Aha! Roadmaps score, what is the actual value of this feature?

For a walkthrough of custom worksheet equations with examples, you may enjoy an introduction to advanced equations.

Click any of the following links to skip ahead:

Syntax

To reference Aha! Roadmaps fields in worksheet equations, you use a custom syntax that represents the field you want to reference.

In this example, the worksheet is comparing a feature's due date with the release's release date and returning a status that will automatically update as those dates change.

Custom worksheet with dates on a feature

To accomplish this in a worksheet equation, a custom syntax is used to refer to the feature’s Due on field and the release’s Release date field. For the example above, the equation looks like this (see Value column).

Custom worksheet builder for features

The equations use custom syntax to give the worksheet directions to the field(s) that you want to access. Those directions are always relative to the location of the worksheet. Let's break down the equation.

Row 1’s equation is self.due_date.

  • Every field reference starts with "self." This is simply a declaration that means, “Starting from this record.”

  • Since the worksheet is on a feature, we now add the identifier for the field we want to reference.

  • The identifier for a feature’s due date is due_date.

  • self.due_date can also be read as “Starting from this record (feature), reference the Due date field on the record.”

Row 2’s equation is self.release.release_date.

  • This worksheet is located on a feature, but now we want to reference a field on the feature’s release.

  • The identifier for a release’s release date field is release_date. The syntax self.release_date wouldn’t work here because release_date is not a feature field.

  • To look at the feature's release, we will include release before release_date to indicate what field we’re referencing relative to the record we’re on.

  • self.release.release_date can also be read as “Starting from this feature, go to the feature’s release, then reference the Release date field.”

Worksheet field




Feature complete

self.

due_date



Starting at the feature...

...return the feature's Due date field.


Release date

self.

release.

release_date


Starting at the feature...

...go to the feature's release...

...and return the feature's release's Release date field.

Here are additional rules to be aware of when referencing fields:

  • Every field value starts with "self."

  • If you are referencing a field on the record, you must add the identifier for the field name after self. Use the a complete list of fields for each record type to find your field.

    • For example: self.score

  • Field references are case sensitive.

  • To reference a field on a linked record, add the linked record type along with the field.

    • For example: self.release.release_date

  • You can only reference fields that are on the record or linked to the record where the worksheet resides.

  • If you are referencing a field which includes more than one value (an array), then your field name will need to be followed by open and closed brackets. If you’re unsure about whether or not brackets are required, please review the field identifier list. Without a function, arrays will return in a comma-separated list. Arrays can be combined with functions to calculate the value on the arrays.

    • For example:

      • self.requirements[].name this returns requirement names for all requirements on the feature

      • count(self.requirements[]) returns the total count of requirements on the feature.

  • If you want results in an array to only include specific values, you can filter results based on certain criteria. You can read more about how to filter results.

    • For example: count(self.requirements[self.status == “Shipped”]) returns the total number of requirements that are in the “Shipped” status.

  • If you are referencing a custom field, you must include a reference to the API Key that is defined on the Settings ⚙️ Account Custom field page.

    • For example: self.custom.API-KEY

  • To reference a specific scorecard metric or a custom worksheet value, include the name of the metric at the end of the syntax surrounded by curly brackets.

    • For example:

      • self.score{“Sales increase”} this returns the value of the “Sales increase” metric in the record’s scorecard.

      • self.custom.API-KEY{“Name of your worksheet field”} this returns the value of the worksheet field.

Click here for the complete list of fields that can be referenced for each record type.

Top

Equation examples

Calculate feature value from votes and score

In this example, a feature worksheet is calculating a total prioritization score by multiplying the number of votes received for the feature’s idea with the Aha! Roadmaps score.

Custom worksheet calculating custom score

  1. Navigate to Settings ⚙️ Account Custom fields.

  2. Select the Features tab and click Add custom field.

  3. Choose Worksheet and click Next.

  4. Add a unique Name for your custom field.

  5. Worksheet section

    • Row 1

      • In the Field name column, enter “Aha! score” — it’s important to choose a unique value here if you want to reference this field in another calculation at a later time.

      • In the Value column, begin typing to launch the Equation builder. Add the following syntax to reference your feature’s Aha! Roadmaps score: self.score

    • Row 2

      • In the Field name column, enter “Ideas votes”.

      • In the Value column, begin typing to launch the Equation builder. Add the following formula to add the total ideas votes: sum(self.ideas[].votes).

    • Row 3

      • In the Field name column, enter “Total score”.

      • In the Value column, begin typing to launch the Equation builder. Add the following formula to add the total ideas votes: field("Aha! score")*field("Ideas votes")

      You can also bring in predefined choice fields from an idea's proxy votes, such as customer priority.

  6. Add the custom field to your layout. If you are already using feature layouts, select the Use in layouts radio button and then select your layout. If you don’t yet have a field layout, select the Use in workspaces option to create a new layout for the features in your workspace.

Top

Custom worksheet for a prioritization score

Roll up feature costs to your initiatives

In this example, an initiatives worksheet is calculating the total cost of feature development and presenting it on the initiative.

Initiative details view with custom worksheet

This example assumes that feature cost is being captured via a custom field on features. The custom field’s API key is feature_cost.

    1. Navigate to Account Custom fields.

    2. Select the Initiatives tab and click Add custom field.

    3. Choose Worksheet and click Next.

    4. Add a unique Name for your custom field.

    5. Worksheet section

  • Row 1

    • In the Field name column, enter “Cost."

    • In the Value column, begin typing to launch the Equation builder. Add the following formula to roll up investment costs to your initiatives: sum(self.features[].custom.feature_cost)

  • Row 2

    • In the Field name column, enter “Revenue." You can leave the Value column blank.

  • Row 3

    • Click Add row so that the worksheet has three rows.

    • In the Field name column, enter “Profit."

    • In the Value column, add the following formula to calculate the difference between rows 1 and 2: field("Revenue") - field("Cost")

Add the custom field to your layout. If you are already using initiative layouts, select the Use in layouts radio button and then select your layout. If you don’t yet have a field layout, select the Use in workspaces option to create a new layout for the initiatives in your workspace.

Custom worksheet builder for initiatives

Top

More equation examples

General

Use

Equation

Notes

Change t-shirt sizes into a numerical value

if(field("DF_Size")=="XS","0.5",

if(field("DF_Size")=="Small","1.6",

if(field("DF_Size")=="Medium","6"

if(field("DF_Size")=="Large","16","40"))))...or...

if(self.custom.tshirt_size == "XS", "0.5",

if(self.custom.tshirt_size == "Small", "1.6",

if(self.custom.tshirt_size == "Medium, 6, 16)))

DF_Size is a custom field to track t-shirt values. In this example, the user wanted to assign values to t-shirt sizes to multiply that by another value later on:

  • XS = .05

  • Small = 1.6

  • Medium = 6

  • Large = 16

  • Extra large = 40

Reference a worksheet field

sum(self.features[].custom.feature_cost{“Total cost”})

This initiative worksheet is configured to sum the Total cost for all its linked features. There is a separate features worksheet that captures a feature's Total cost. The field's API key is feature_cost.

Reference a linked record’s custom field

self.release.custom.gtm_release_date

Feature worksheet referencing a custom date field on the feature’s release. The API key is gtm_release_date.

Reference a scorecard metric

self.score{“Sales increase”}

Feature worksheet referencing an individual scorecard metric named Sales increase.

Reference a record’s custom field

self.custom.GTM

Feature worksheet referencing custom date field. The field's API key is GTM.

Reference a record’s standard field

self.status

Feature worksheet referencing feature status

Reference a linked record’s standard field

self.release.release_date

Feature worksheet referencing the release date on a feature’s release

Roll data up from worksheet embedded in a custom table with the data entered on a feature to parent records

sum(self.features[].custom.feature_savings[].custom.incremental_savings_estimate{"Total”})

incremental_savings_estimate is a custom table on features. Total is a field from that custom table.

Reference a custom field on many linked records

sum(self.features[].custom.feature_cost)

Initiative worksheet that sums a custom number field for all of its linked features. The field's API key is feature_cost.

Reference a standard field on many linked records

sum(self.features[].original_estimate)

Initiative worksheet that sums all of its linked feature’s estimates

Average numbers but do not count zeros, so they do not affect the final average

(Result 1 + Result 2 + Result 3 + Result 4 + Result 5) / (if(Result 1 0, 1, 0) + if(Result 2 0, 1, 0) + if(Result 3 0, 1, 0) + if(Result 4 0, 1, 0) + if(Result 5 0, 1, 0))

Numerator: (Result 1 + Result 2 + Result 3 + Result 4 + Result 5)

Denominator: (if(Result 1 0, 1, 0) + if(Result 2 0, 1, 0) + if(Result 3 0, 1, 0) + if(Result 4 0, 1, 0) + if(Result 5 0, 1, 0))

Reference a custom scorecard's calculated score

self.custom.release_score

Release worksheet referencing a custom scorecard metric on the release. The field's API key is release_score.

Reference a custom scorecard metric

self.custom.release_score{“Sales increase”}

Release worksheet referencing a custom scorecard metric on the release. The field's API key is release_score. The scorecard metric's name is Sales increase.

Convert dollar value of a custom field to a text output based on a comparison

if (field("$$ until goal achieved") 0, "on track","off track")

The $$ until goal achieved is a custom field on a record. In this example, this field holds the amount indicating how much more needs to be obtained before achieving the goal.

  • If less than zero, the output is on track.

  • If no, the output is off track.

Reference a many-to-many relationship custom field

self.custom.<many-to-many field API key>[].custom.<custom field API key>

Worksheet referencing a custom table through a many-to-many relationship custom field. API key will depend on the API key of your many-to-many relationship custom field.

Reference a one-to-many relationship custom field

self.custom.<one-to-many field API key>[].custom.<custom field API key>

Worksheet referencing a custom table through a one-to-many relationship custom field. API key will depend on the API key of your one-to-many relationship custom field.

Convert calculated field to 15/30 day window

if(field("Calculated 1") < "16","15 Day", if(field("Calculated 1") < "31","30 Day", ">30 Day"))


Convert calculated field to 15/30/45 day window

if(field("Calculated 1") < "16","15 Day", if(field("Calculated 1") < "31","30 Day", if(field("Calculated 1") < "46", "45 Day", ">45 Day"))


Convert selected values to numbers

if(self.custom.api_key == "Choice 1", 20, if(self.custom.api_key == "Choice 2", 10, if(self.custom.api_key == "Choice 3", 5, 0)))


Convert T-shirt size to a value

if(field("DF_Size")=="Small","0.5",(if field("DF_Size")=="Medium","1.0","2”))if(field("DF_Size")=="Small", "0.5”,”1.0”)if(field("DF_Size")=="Small",”0.5” OR if(field("DF_Size")==“Medium”,”1.0", "1.5”)1.5 * if(field("Total Development Cost")<100000,1,if(field("Total Development Cost")<2000000,2,if(field("Total Development Cost") <5000000,3,if(field("Total Development Cost")<10000000,4,5))))if(field("DF_Size")=="XS","0.5",if(field("DF_Size")= ="Small","1.6",if(field("DF_Size")=="Medium","6",if(field("DF_Size")=="Large","16","40"))))


Subtract one field from another

field("Savings Goal for this initiative") - field("Total Savings across all features”)


Top

Initiatives

Use

Equation

Notes

Rolling up feature cost to initiatives

  • Initiative worksheet: sum(self.features[].custom.feature_cost)

  • Product line initiative worksheet: sum(self.children[].custom.feature_rollup_to_initiative{"Feature cost rollup"})

  • Top level initiative worksheet: sum(self.children[].custom.initiative_rollup{"Rollup from Sub-Initiatives"})

This worksheet includes a custom field named Feature cost. Feature cost will roll up to linked initiatives.

Top

Releases

Use

Equation

Notes

Calculate a release's remaining capacity on each feature

  • Release capacity: (self.release.total_capacity)*1440

  • Used capacity: sum(self.release.features[].original_estimate)/480

  • Remaining capacity: field("Release capacity")-sum(self.custom.planning_metrics{"Used capacity"})

Worksheet that looks at a feature's release and the estimates of all other features in the release, then returns the remaining available release capacity.

Roll up custom field total of all releases linked to a parent record.

sum(self.releases[].custom.dfmanhours)

This worksheet should be on a parent record of the release. The API key for the release custom field in the example is named dfmanhours.

Find dates between release phases to determine overall time to market

  • self.release_phases[self.name == "Portfolio Introduction"].start_on

  • ​​self.release_phases[self.name == "General Availability"].end_on

  • datediff(field("Portfolio Start"),field("GA End"),"days")


Top

Epics, features, and requirements

Use

Equation

Notes

Roll up the total cost of all features linked to a parent record

sum(self.features[].custom.df_savings)

This worksheet should be on a parent record of the feature. The feature cost is held in a feature custom field referenced as df_savings in the example.

Roll up a count of all features linked to a parent record

count(self.features[])

This worksheet should be on a parent record of the feature.

Reference feature scorecard metrics

self.features.custom.df_balanced_scorecard{“Number of LinkedIn Articles”}

This worksheet references a custom scorecard, configured as follows:

  • Name: DF_Balanced Scorecard

  • API Key: df_balanced_scorecard

  • Metrics:

    • Number of LinkedIn Articles

    • Number of Events

    • Used Internally

Rolling up feature cost to initiatives

  • Initiative worksheet: sum(self.features[].custom.feature_cost)

  • Product line initiative worksheet: sum(self.children[].custom.feature_rollup_to_initiative{"Feature cost rollup"})

  • Top level initiative worksheet: sum(self.children[].custom.initiative_rollup{"Rollup from Sub-Initiatives"})

The feature's custom field is Feature cost. Feature cost will roll up to linked initiatives.

Find all features with "partner" in the title

if(strpos(field("Feature name"),"Partner")<0,"No","Yes")

strpos returns -1 if the string isn't found, so this checks to see if the result is less than zero. This can then be filtered and/or brought into a pivot for better readability.

Note: You can also do this via the [Record] Name search filter in Aha! Roadmaps reports.

Top

To-dos

Use

Equation

Notes

Turn to-do statuses into Done or Not done

if (field("To-do status")="Complete", "Done", "Not done")

There are three to-do statuses: Complete, Pending, Overdue. But there is no way with the default report filter to just show those that are not complete (Pending and Overdue). Use this equation as a calculated column in a list report to do that.

Then, rename the calculated column to something meaningful, and filter your report for your chosen to-do status.

Top

If you get stuck, please reach out to our Customer Success team. Our team is made up entirely of product experts and responds fast.