Show day of week of Created field in filter

[Michael Bevilacqua]

I've created a filter and want a column returned that displays the Day of Week for the Created field.

Story Type - business

Created - 25/Jul/23

Day Of The Week (Created) - Tuesday <------------- This is the column I want

[Ste Wright]

Hi @Michael Bevilacqua 

You'll need a field to refer to in search, and to add as a column. You could though...

• Add a field to capture the day
• Use Automation to populate it.

See how I'd do it below!

---

Custom Field:

You'll need to a Product Admin, and then...

1. Go to Settings (cog icon in top-right) > Issues
2. Select Custom Fields from the left-hand menu
3. Press the "Create custom field" button
4. Choose a "Select List (single choice)" field, adding the days of the week as options
5. Give the field a name - eg. Day of Week
6. Either leave it off the screen, or add it to a view-only version of the screen, as it will only be set via automation

Note: I'd use a shared field even if this is a Team-managed Project, so the field is consistent across all Projects.

---

Automation Rule:

• Trigger: Issue Created
• Action: Create Variable
  • Name = DayOfWeek
  • Smart Value = {{issue.created.fullDate}}
• Condition: IF Block
  • IF...
    • Sub-Condition: Advanced Compare Condition
      • First Value = {{DayOfWeek}}
      • Condition = Contains
      • Second Value = Monday
    • Sub-Action: Edit Issue
      • Field = Day of Week
        • Value = Monday
  • ELSE-IF...
    • Sub-Condition: Advanced Compare Condition
      • First Value = {{DayOfWeek}}
      • Condition = Contains
      • Second Value = Tuesday
    • Sub-Action: Edit Issue
      • Field = Day of Week
        • Value = Tuesday
  • ELSE-IF...
    • Sub-Condition: Advanced Compare Condition
      • First Value = {{DayOfWeek}}
      • Condition = Contains
      • Second Value =
    • Sub-Action: Edit Issue
      • Field = Day of Week
        • Value = Tuesday
  • ELSE-IF...
    • Sub-Condition: Advanced Compare Condition
      • First Value = {{DayOfWeek}}
      • Condition = Contains
      • Second Value = Wednesday
    • Sub-Action: Edit Issue
      • Field = Day of Week
        • Value = Wednesday
  • ELSE-IF...
    • Sub-Condition: Advanced Compare Condition
      • First Value = {{DayOfWeek}}
      • Condition = Contains
      • Second Value = Thursday
    • Sub-Action: Edit Issue
      • Field = Day of Week
        • Value = Thursday
  • ELSE-IF...
    • Sub-Condition: Advanced Compare Condition
      • First Value = {{DayOfWeek}}
      • Condition = Contains
      • Second Value = Friday
    • Sub-Action: Edit Issue
      • Field = Day of Week
        • Value = Friday
  • ELSE-IF...
    • Sub-Condition: Advanced Compare Condition
      • First Value = {{DayOfWeek}}
      • Condition = Contains
      • Second Value = Saturday
    • Sub-Action: Edit Issue
      • Field = Day of Week
        • Value = Saturday
  • ELSE-IF...
    • Sub-Condition: Advanced Compare Condition
      • First Value = {{DayOfWeek}}
      • Condition = Contains
      • Second Value = Sunday
    • Sub-Action: Edit Issue
      • Field = Day of Week
        • Value = Sunday

---

A few notes on this approach:

• Using IF/ELSE-IF was the most stable method I found to do this, and given there's only 7 possible routes it felt suitable to do it per day.
• I used a Select List field, rather than a text field, so it could be used in dashboards, easily used in JQL, etc.
• You could set the Day of Week for previously created issues also if you'd like (this will updated the issue's updated date) - to do this...
  • Create a Copy of the rule above
  • Change the original Trigger to be "Scheduled"
  • Check the box "Run a JQL search and execute actions for each issue in the query"
  • Put in a suitable JQL query - eg.
    • All Issues - project = ABC
    • All Incidents - project = ABC and issuetype = Incident
    • Just Incidents from 2023 - project = ABC and issuetype = Incident and created >= startOfYear()
  • Uncheck the box "Only include issues that have changed since the last time this rule executed"
  • Publish the rule and run it
  • Once all fields are populated, disable and/or delete the rule. It's only needed once.

---

Let us know if this works for you!

Ste

[Bill Sheboy]

Hi @Ste Wright 

This could also be done using the date/time formatting functions to capture the day of week for the date, without the need for the if/else. 

And when a select field was still desired for the day of week field, that could be converted from text to select field, id value with a Lookup Table.

Kind regards,
Bill

[Michael Bevilacqua]

@Bill Sheboy What is the date/time formatting function to capture Day of Week?

[Michael Bevilacqua]

How do I reference the variable named TheDayOfWeek in the automation rule?

[Bill Sheboy]

Please review the data/time formatting information here: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#--

That describes the use of the format() function, including a reference to the supported DateTimeFormatter values.  For example, the one for the complete day of week name is:

{{issue.created.format("EEEE")}}

This can be used directly or once that is stored in the variable, a variable can be referenced by using the name:

{{myExampleVariableName}}

[Ste Wright]

Hi @Bill Sheboy 

Nice! I forgot about that date/time value! The lookup table was also excellent, I've not worked with those before.

@Michael Bevilacqua - I tried Bill's recommendations, so there's two options here.

---

Text Field:

If you're just using a text field, not a select field, this rule should work:

• Trigger: Issue Created
• Action: Edit Issue
  • Field = Day of Week
    • Value = {{issue.created.format("EEEE")}}

---

Select List Field:

You can avoid the IF/ELSE statements, but will need two custom fields:

• Short text field - eg. Day of Week (Text)
• Select list field - eg. Day of Week

You can then create the rule below - a few notes on this approach:

• The rule adds the name of the week to the text field first (same as above), then uses a lookup table to check what that day is, before applying the select list option dynamically without the IF/ELSE statements.
• You'll need the select list field's custom field ID, and each select list option's option ID also. The custom field ID is better to use than the name, in case you edit the name in future.
• In the below table...
  • The "Values" - 10001 > 10007 - are the option IDs
  • The custom field ID is 10123 below

Rule:

• Trigger: Issue Created
• Action: Edit Issue
  • Field = Day of Week (Text)
    • Value = {{issue.created.format("EEEE")}}
• Action: Re-fetch issue data
• Action: Create Lookup Table
  • Table Variable Name = dayofweektable
    • Table Row 1:
      • Key = Monday
      • Value = 10001
    • Table Row 2:
      • Key = Tuesday
      • Value = 10002
    • Table Row 3:
      • Key = Wednesday
      • Value = 10003
    • Table Row 4:
      • Key = Thursday
      • Value = 10004
    • Table Row 5:
      • Key = Friday
      • Value = 10005
    • Table Row 6:
      • Key = Saturday
      • Value = 10006
    • Table Row 7:
      • Key = Sunday
      • Value = 10007
• Action: Edit Issue
  • Field = Day of Week
    • Value = {{dayofweektable.get(issue.customfield_10123)}}

---

Let us know what you think!

Ste

[Michael Bevilacqua]

Hey Bill,

Thanks for the thought! I'd like a report showing which day of the week and issue was created. Our team thinks we experience production incidents mostly on Fridays, so I wanted to see if that is the case based on the Day of Week of the issue create date.

[Bill Sheboy]

Hi @Michael Bevilacqua 

That is not possible with the out-of-the-box JQL features.

What problem are you trying to solve by doing this?  Knowing that may help the community to offer suggestions.  Thanks!

Kind regards,
Bill

[Bill Sheboy]

Thanks for that information, Michael.  Is this seems information to support a short-term (and not permanent) investigation, perhaps consider using the advanced search to find all issues for a time range, export them to a spreadsheet, and then perform the analysis there.