Sample Formulas - Markdown

Change Text Color

This example uses color-coded text to let users know when issue due dates are approaching (or overdue).

CODE

IF dueDate < today(): 
  ":panel[OVERDUE]{color=red}" 
ELSE IF DAYS_BETWEEN(today(), dueDate) <= 7: 
  ":panel[Due Soon]{color=green}" 
ELSE IF DAYS_BETWEEN(today(), dueDate) > 7: 
  ":smile:" 
ELSE: 
  ":panel[Needs Due Date]{color=blue}"

Change Background Color

This example changes the background color of the cell, according to the status category, and returns the summary text for each issue.

CODE

WITH format(text, color) = """:panel[${text}]{backgroundColor=${color}}""" :

CASE(status.category,
  "To Do", format(summary, "gray"),
  "In Progress", format(summary, "blue"),
  "Done", format(summary, "green")
)

You can easily customize this by changing the return value, altering the colors, or combining this effect with a larger formula.

Try experimenting with text and background color combinations - this can be a great way to draw attention to key data points.

Customizable Progress Bars - Based on a Custom Progress Field or Function

The following formula creates custom progress bars based on a custom progress field or user function.

Structure with custom progress bars based on issue status

We used the following formula to build the custom progress bar:

CODE

WITH simpleProgressBar(progress, maxProgress, stepCount) = (
  WITH _bars(count, emoji) = REPEAT(emoji, count):
  WITH doneBarsCount = FLOOR(progress / maxProgress * stepCount):
    _bars(doneBarsCount, ":green_square:") CONCAT _bars(stepCount - doneBarsCount, ":white_large_square:")
):


simpleProgressBar(customProgress, 1, 10)

Starting with this, you can tailor the progress bar to your team's particular needs.

Colors can easily be configured by altering the "color" values - in this case, we used green and white squares.
The stepCount value can be adjusted depending on how many squares you want to visualize.
The progress calculation (customProgress) can be based on a custom field or custom user function. In the following example, we added a custom user function to mimic the default calculation in our Progress by Status column:

CODE

WITH simpleProgressBar(progress, maxProgress, stepCount) = (
  WITH _bars(count, emoji) = REPEAT(emoji, count):
  WITH doneBarsCount = FLOOR(progress / maxProgress * stepCount):
    _bars(doneBarsCount, ":green_square:") CONCAT _bars(stepCount - doneBarsCount, ":white_large_square:")
):


WITH customProgress = AVG {CASE(Status; "Done"; 1; "In Progress"; .5; "To do"; 0)}:
simpleProgressBar(customProgress, 1, 10)

Customizable Progress Bars - Based on Status Categories

The following formula calculates progress based on the Status Categories of the issue and its children.

Structure with custom progress bars based on status categories

CODE

WITH multiProgressBar(progressArray, maxProgress, emojiArray, stepCount) = (
  WITH _bars(count, emoji) = (IF count > 0: REPEAT(emoji, count) ELSE ""):
  WITH barCountsWithInitial =
    ARRAY(
      ARRAY(ARRAY(ARRAY(0, maxProgress, stepCount))),
      progressArray)
   .FLATTEN() // imitate foldl with initial value
   .REDUCE( (prevArray, p) ->
 	WITH s = prevArray.LAST().get(1):
  	WITH k = prevArray.LAST().get(2):
  	WITH pCount = if (s = 0; 0; FLOOR(p / s * k)):
  	WITH newElement = ARRAY(pCount, s - p, k - pCount):
  	ARRAY(prevArray, ARRAY(newElement)).FLATTEN())
   .MAP(a -> a.get(0)):
  WITH barCounts = barCountsWithInitial.SUBARRAY(1, barCountsWithInitial.SIZE()):
  progressArray.INDEXES()
	.MAP(_bars(barCounts.GET($), emojiArray.GET($)))
	.JOIN("", "", "")
):
WITH all = COUNT#truthy { statusCategory }:
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), all,
  ARRAY(":white_large_square:", ":blue_square:", ":green_square:"),
  20
)