Group Ideas by 'date' fields

[Stephane De Keerle]

Problem:

My team is looking to group ideas by 'Project Start Date' which uses the 'date' field format in our 'list' view.

I want to end up with a backlog with swimlanes for each quarter:

• FY23 Q4, FY24 Q1
• Oct-Dec, Jan-March, April-June

This will help me visualise what's in progress currently, as well as what's prioritised for the next quarter.

What do we do now:

• Everyone in the org uses 'Project Start Date' which uses the 'date' field to define the start date for their specfic 'list' view
• We also use the out of the box JPD 'Roadmap' field with Now, Next, Later, when communicating out to the wider org.
  
  

Questions:

• How do I group by 'date' fields?
• If I can't, will grouping by 'date' fields be added to the roadmap?
• Do you have any alternative suggestions in the meantime.
• Are there any drawbacks from a reporting pov to using a 'Select Field' option to just manually create FY23 Q4 FY24 Q1 etc.

[Tanguy Crusson]

Thanks for the suggestion @Stephane De Keerle . It's not something we've added yet as there are a bunch of complications. E.g. if you have ideas that have a single date, others with a month, others with a quarter - and you group by that field, then it needs to make sense and there are a bunch of corner cases. We've put it in the "to be figured out later" bucket while we focus on other things for the moment. 

In the meantime, you could use a select field. You'd just need to maintain them manually and this could mean some double entries. But maybe you can go around that by configuring an automation rule (I haven't tried) that automatically updates this field whenever the date field is changed

[Moran Bachar]

Hi Tanguy,

Thanks for the explenation.

My 2 cents are in this case, to allow 2 types of Date fields, one is like the Date field you have now, and a new one, that will just be a date (not start and end, rather one single date, and in a date format, rather than the current text format).

I myself am struggling with grouping by Date fields (which honestly seems like a basic feature to me), and while researching here in the community and in other places on the web, it does seem like this issue is common to many other JPD users.

Please do update if it is already on the roadmap for you guys at JPD.

[Harry Brant]

Hi @Stephane De Keerle

Not sure if you're still looking for a solution, but I recently faced the same issue when migrating our marketing teams from Monday.com to JPD and subsequently developed a workaround, which is essentially an automated version of Tanguy's recommendation. Since I came across your post whilst initially researching the problem, I figure I'll describe my solution here in case it can help others until Atlassian officially add support for grouping by month.

The reason why grouping by date fields is currently not supported is uniquely in JPD, Atlassian store dates as a "start:xxx, end:xxx" string, not as an actual date. I suspect this is so they can support displaying the month or quarter name in the same date field, but it also means you can't use normal date manipulation functions, including automation. As such, the workaround involves extracting the date portion of the string, converting it into a date value, reformatting it as the month name, and then updating a picklist field with the month name value. That picklist field then becomes the "grouping" field. Thanks to @František in this thread for detailing the format of the JPD date fields.

Here's how to set it up:

1. Create a select field (e.g. "Month"). Populate it with the entire list of month names. Add it to the view for testing, you can hide it afterwards.
2. Create an automation rule:
   1. Trigger = [date field] value changed (in your case "Project start").
   2. Action = Create variable called "startDate". Value = {{issue.[date custom field ID].substringBetween("start\":\"","\",")}} (Replace [date custom field ID] with the ID of your date field e.g. customfield_14178). This extracts the actual date string from the field value.
   3. Action = Create variable called "startMonth". Value = {{startDate.toDate("yyyy-MM-dd").format("MMMM")}} This converts the extracted date string to a date value and formats it to just the month name.
   4. Action = Edit issue fields. Field = "Month". Value = {{startMonth}}. This sets the idea's month field to the extracted month name.
3. Test by triggering the rule and confirming the "Month" field is set correctly to match the date field's month. Once you've confirmed the rule works, set the view to group by the "Month" field and hide the field in the view.

Here's some screenshots of my rule for reference (ignore the audit log actions, I added those for testing):

Hope this is helpful.

[Tanguy Crusson]

Thanks @Harry Brant , I added a reference to your workaround in the FAQ

[Harry Brant]

Thanks @Tanguy Crusson, glad it's useful!

Though "Harry Grant" might be a little confused why you've tagged him :)

[Tanguy Crusson]

Ha!

[Moran Bachar]

That is a great idea Harry, thank you.

I faced a similar problem and ended up extracting the date from the date field into a variable that represents the date in a YYYYMMDD format, and I filter and group by that field.

It works nicely for our use case for now, though I think your suggestion might be more classy visually and less error prone (not dealing with large numbers like mine).

In our scenario, we have a release twice a month (that is regularly on Mondays every 2 weeks). Do you have any suggestion on how to use your idea, but be able to name the 2 releases as "Jan 1st release" and "Jan 2nd release" for example?

Also wondering if you take into account different years (meaning the difference between Jan 2024 and Jan 20205 fr example).

Thanks!