How to use startofweek() dynamically depending on what the current day is

Question

Hi all,

I have a jira dashboard that includes a gadget searching for any item that changed to "ready" status during the last week. I have another gadget for items that changed to "ready" during this week. I realize Jira's startofweek = this last Sunday and endofweek = this coming Saturday.  However my team wants our metrics to reflect as the start of week being Tuesday through Monday. The problem is once current day becomes Sunday and Monday, my gadget refreshes and thinks a new week has started. Example is today is 8/28/23. On Saturday 8/26/23 if I looked at my dashboard gadget for Last Week it would pull items that moved to Ready during 8/15-8/21 which is what I want. But now when I look at the dashboard gadget for Last Week on Monday 8/28, its showing me items that moved to Ready during 8/22-8/28 which is not what I want. I still want to see items that moved to Ready during 8/15-8/21. Then when Tuesday 8/29 becomes current day then that's when I want the gadget to reflect the beginning of a new week so when I look at Last Week gadget it will show items that moved to Ready during 8/22-8/28.

The first bolded part is my way of saying if today is Sunday or Monday then filter issues that go back 2 weeks. The 2nd bolded part is me saying if Today is Tuesday-Saturday then filter issues that go back only 1 week prior.

The problem is.. jira doesn't like my use of "Now()". I have read so many posts and stuck at this point.

(now()=startofweek() or now()=startofweek("+1d") AND project in (SBS, SBAM, SBILL, SBWS, SBUN) AND issuetype = Story and status CHANGED TO "Ready" During (StartofWeek("-12d"),endofweek("-12d"))) OR (now()>=startofweek("+2d") AND project in (SBS, SBAM, SBILL, SBWS, SBUN) AND issuetype = Story and status CHANGED TO "Ready" During (StartofWeek("-5d"),endofweek("-5d")))

Bill Sheboy · Answer

Yes, I think I got that, and...context is key.  Instead, I was noting having 2 dashboards:

* If you build a report dashboard which always shows "last week" relative to now, it would be from whatever last Tuesday was until this current week's Monday: 22 August -- 28 August, inclusive. 
* When you want a report for "this week" have a separate dashboard for that: from this week's Tuesday...even though some of the dates are in the future: 29 August -- 4 September, inclusive.

I do not believe this information can be shown on a single dashboard without changing the filter (or using 2 filters for different dashboard gadgets).  (A possible work-around is to do exactly that: have an automation rule which changes the filter contents when the "week" ends, using a scheduler and a call to the REST API.)

There are marketplace addon dashboard gadgets that allow setting parameters for a page, but I was ignoring those as they may not help with the status change history part.  Perhaps look at the Atlassian Marketplace to learn if there are reporting addons which support changing when weeks start / end.

Justin Princehorn · Answer

Thanks Bill. I'm not sure if I'm following your suggestion. I understand how to use the startofweek() and endofweek(). But the problem is when you run the jql on a Sunday and Monday, it brings back the same results as if I run the jql on Tuesday too. I want Sunday and Monday to act like I'm running the jql statement at the END of a reporting week as if it was a Saturday.

Probably easier to explain it this way...  Let's say today is Thursday and I want to see what things changed for this week (Tuesday through Monday). I know to look at things After startofweek(+2). The problem is 3 days from today (Thursday) it will be Sunday. And Startofweek will no longer be last Sunday's date it will be the Sunday date we are on.

Bill Sheboy · Answer

Hi @Justin Princehorn

JQL is not a SQL, and so is quite limited and specific in its syntax.  JQL's purpose is to find Jira issues, and so (in general) all the left-side things need to be an issue field.  Like this:

someField someOperator someValue

And, startOfWeek() already accounts for "now"...or it could not know which week to check.

Perhaps remove those initial values, and just use relative dates for start and end of week to show "last week" was from Tuesday to Monday:

created >= startOfWeek("-5d") AND created <= startOfWeek("+1d")

And adjust for your JQL to use those your status CHANGED expressions instead.

Kind regards,
Bill