How do I get a count of all sub-items in a structure by status category?

[Matthew Mallard]

I have a a multi-level structure that shows a bunch of levels. I am trying to figure out a formula that will count the number of issues under a level that are in a specific status category. This would allow a individual to understand how far a long a top-level issue is by seeing a count of how many issues under it are Done, In Progress, or To Do.

[David Niro]

Hello @Matthew Mallard ,

David from ALM Works here.  Are you currently on Cloud or on-prem?

We have some useful formula examples in our wiki.  I believe the Customizable Progress Bar may be what you are looking for, if you are on-prem.

If you are on Cloud, we would need to modify the formula to remove the wiki markup items.  and replace the green squares with some character to indicate issue complete. 

Please let me know if this helps.

Best,
David

[Matthew Mallard]

Hey there,

I am on-prem, data center 8.12.2, structure is version 7.3.0.

I tried using the status multi-bar example but am unsure what field I should select for granularity. I'm going to be honest, I'm fairly new to these formulas and this is probably a pretty complex one to start out with.

Here's the formula, modified the colors and field just a bit:

//stepCount - length of the bar chart in characters
WITH multiProgressBar(progressArray, maxProgress, colorsArray, colorForRemaining, stepCount) = (
WITH _bars(count, color) = (IF count > 0: """{color:$color}${REPEAT("▮", count)}{color}""" ELSE ""):
WITH barCounts = progressArray.MAP(FLOOR($ / maxProgress * stepCount)):
progressArray.INDEXES()
.MAP(_bars(barCounts.GET($), colorsArray.GET($)))
.MERGE_ARRAYS(_bars(MAX(0, granularity - barCounts.SUM()), colorForRemaining))
.JOIN("", "", "")
):

WITH todo = COUNT#truthy { statusCategory = "To Do" }:
WITH inProgress = COUNT#truthy { statusCategory = "In Progress" }:
WITH done = COUNT#truthy { statusCategory = "Done" }:

multiProgressBar(
ARRAY(todo, inProgress, done), COUNT { 1 },
ARRAY("gray", "blue", "green"), "orange",
20
)

[David Niro]

Hello @Matthew Mallard ,

Granularity just helps to define the length of the bar section.  If you leave it blank, it will default to the barCounts variable defined earlier in the formula.  

Feel free to reach out to our support team directly via email support@almworks.com or customer portal support.almworks.com.

Best,
David

[Matthew Mallard]

Thank you David!