Change Text Color
This example uses color-coded text to let users know when issue due dates are approaching (or overdue).
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.
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.
We used the following formula to build the custom progress bar:
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:
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.
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 )