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

Question

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.

Matthew Mallard_m7eijj · Answer

Thank you David!

David Niro · Answer

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_m7eijj · Answer

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
)