How to create a rich filter to sum up to different numeric fields: Story Points and Test Estimation

[Richard Milea]

I need to know how to sum up to different numeric fields using a rich filter and which rich filter gadget to use.  I want to sum up the total story points using the "Story Point" field and then sum up the "Test Estimation" numeric field.  To give me a total story point including test estimation.  So I can display one number: total story points in a gadget on a dashboard. 

[James Gamble]

Hola Richard,

Yes, Rich Filters can do this, but the cleanest way is to create a Custom Value rather than having the gadget add two separate displayed counters. Your goal is effectively:

sum(Story Points) + sum(Test Estimation)

for the same set of work items.

In the Rich Filter configuration, go to Rich Filters > your rich filter > Custom Values > Create custom value. Configure an aggregation formula that combines the Story Points numeric field with your Test Estimation numeric field, then use that custom value in a Rich Filter Simple Counter gadget. Appfire’s Cloud documentation confirms that Custom Values support aggregation formulas based on numeric fields, and that Simple Counter can display a custom value as a single dashboard result.

I’d use the Simple Counter rather than a gauge because you’re asking for a single absolute total, not a percentage or progress relative to another value. The counter gadget is specifically designed to display field sums or custom-calculated values as standalone numbers.

One thing I’d check before building it is whether Story Points and Test Estimation are genuinely expressed in the same unit. If Test Estimation represents hours while Story Points are relative estimates, adding them mathematically will produce a number, but it won’t have a meaningful planning interpretation.

Thanks,

James

[Richard Milea]

This is good news.  But I have one problem that I can't seem to figure out.  I don't want to count the story points for the JIRA's that have a value in the test estimate field.  How would I handle this in the JQL of the rich filter?  Here is my JIRA filter JQL:  

project = RAPIDS2 and component = RAPIDS and Sprint =95717 and labels in (ETL) and status != Done or

Sprint =95716 and labels in (ETL) and status != Done and cf[21701] is not EMPTY  - The JIRA's that are returned in the filter that pass this logic will also have a value in the Story Point field.  And these I do not want to be added up in my calculation.  

[Ivan Manolov _Appfire_]

Hello @Richard Milea,

On the follow-up question, there are two separate things going on, and it's worth splitting them.

The JQL first. Mixing AND and OR without parentheses is risky: Jira evaluates the statement left to right when you don't group it, so the query is almost certainly not matching what you intend. If the sprint condition is the part that varies, write it explicitly:

project = RAPIDS2 AND component = RAPIDS AND labels in (ETL) AND status != Done
AND (sprint = 95717 OR (sprint = 95716 AND cf[21701] is not EMPTY))

If you meant something different by the second half, the fix is the same idea: put every OR branch in its own set of parentheses and keep the shared conditions outside them.

The conditional sum. JQL is a filter language, so it can't express "use Story Points here, Test Estimation there" inside one query. What it can do is separate the two populations for you, and then you add two numbers instead of one:

• ... AND cf[21701] is EMPTY and sum Story Points over that filter.
• ... AND cf[21701] is not EMPTY and sum Test Estimation over that filter.

Two counters on the dashboard, or one counter per filter with the totals added by whatever gadget you settle on. It also has the advantage of being auditable: you can click into either filter and see exactly which work items fed which half of the total.

Hope this helps,

Ivan

[Ivan Manolov _Appfire_]

Following up on my earlier answer, @Richard Milea: if you'd rather keep this as one number instead of two filters, and you're open to solutions from the Atlassian Marketplace, the app my team and I work on, JXL for Jira, can express the condition per work item.

JXL is a spreadsheet/table view for your Jira data, and it includes formula columns. Your rule becomes a single formula:

IF(ISSET(this.testEstimation), this.testEstimation, this.storyPoints ?? 0)

So each row contributes its Test Estimation when it has one, and its Story Points otherwise. Put a sum-up on that column and you have the combined total, with no double counting. The exact field identifiers come from the editor's autocomplete, since they follow your own field names.

Sheets can also go on a Jira dashboard, though to be precise you'd get the table with its total row rather than a bare single figure, so if a standalone counter is the goal, the two-filter approach may still suit you better.

Best,

Ivan

[Danut M _StonikByte_]

Hi @Richard Milea,

Definitely you should try the steps provided by James gamble first. But if you don't manage to achieve this with Rich Filters or don't like the way the value is diplayed, as an alternative, you could consider the Advanced Issue Filter Formula gadget offered by our Great Gadgets app.

This gadget can display this information very easily.

To get this the gadget should be configured like this:

If you want to display only the total, configure the gadget to display only the a+b.

Danut.