Return true if work has NOT been logged in the last 24 hours?

merritta87 November 16, 2017

Every day we hold a meeting to discuss the work we are working on, look at a burn down chart, and go over how much work is left over. This becomes difficult when people do not log their time daily to the tickets they are working on. 

So, is what I want is a query that sets the "card color" to say, red, if work has NOT been logged in the last 24 hours. I have tried:
NOT worklogDate() > -1d
Which I would assume would return true if work was not logged in the last 24 hours. Which would then set the card color of that entry. But all that does is check for issues with comments in the work log entries outside of the past 1 day. So, if a user has logged time for Monday and Tuesday, but not Wednesday and today being Thursday it doesn't work it returns a false because it found work logs. 

So, how do I get my card color to change based on my example above? 

2 answers

2 votes
Sanja_Segan August 18, 2019

Solved by using 2 filters:

- the first one that looks for tickets that have at least one worklog entry in the last day

worklogdate >= -1d

- the second one that excludes tickets found by the first one:

 Filter != [filter-id]
1 vote
Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 17, 2017

Hi Aaron,

I think I understand what you are looking for here, but there is another possibility here that the issues could have another value besides >, <, or = to worklog values:  NULL.  That is to say issues that have never logged any work at all will still not be returned by your current JQL query.

Instead I'd recommend keeping your existing NOT query, but then also adding in the inverse query without the NOT operator to look like this to start:

(worklogDate <= -1d AND NOT worklogdate > -1d)

Because it's possible that issues have lots of different worklog entries, and

 

But then in order to check for issues that don't have any worklogs at all, you can use the

timespent is empty 

And you can put these together with an OR statements so in the end you can use something like:

(worklogDate <= -1d AND NOT worklogdate > -1d) OR timespent is empty 

 

This way you should be able to create a single query that shows both the issues without recent worklogs and those without any logged time on them.  In my testing I wasn't able to reproduce this problem you mentioned in regards to whether or not a comment is left on the worklog.  At least in my testing the same number of issues were returned here for me regardless of whether or not they had a comment in that worklog itself.

 

Let me know if this helps.

Regards,
Andy

merritta87 November 28, 2017

I think this is actually working perfectly for me. I haven't found any outliers yet. Thank you so much. 

merritta87 November 29, 2017

Sorry, I think I misunderstood the query yesterday. Let me clarify a bit what is happening and see if you can't help some more. 

So, if a ticket has work in the last 24 hours I want it to get a card color. I don't care about any work logged greater than 24 hours ago.

The following query will return records for work logged at 24+ hours? 

worklogDate <= -1d

 And this one for work logged not in the last 24 hours?

NOT worklogdate > -1d

So ANDing them, isn't that kind of saying the same thing? 

I tried this query for finding work logged only in the last 24 hours and I think it is working, maybe you can weigh in on if you think it's a good alternative or not? I believe this will return true if no records are found in that time frame? 

worklogDate not in (-1d, now())

I'm afraid it is just going to end up being the same issue as returning worklogDate(s) outside of the time frame like the following query does?

NOT worklogdate > -1

 

Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 29, 2017

The following query will return records for work logged at 24+ hours? 

worklogDate <= -1d

 And this one for work logged not in the last 24 hours?

NOT worklogdate > -1d

So ANDing them, isn't that kind of saying the same thing? 

Technically no, it is not exactly the same thing.  These queries will not always return the same list of issues.   They might return the same list of issues sometimes, but there exists a possibility that individual issues in Jira have multiple worklog entries on them.   Which in turns means any issues can match one query, the other query, both queries, or neither query when these are paired together. 

So they are not logically the same always.  JIRA's JQL is designed to return Jira issues.  So the first query is generating a list of all the issues in Jira that have at least one worklog entry more than 1 day old.   However the 2nd query is excluding issues that also have at least one worklog entry in the last day.

Each issue in Jira could have lots of different worklog entries from several different days.  It's possible you have issues in Jira that have both worklogs from last week and from today for example.  So if you only ran the 1st query, that issue still gets returned, even though it also has a worklog entry from today.  That's why you need the second query to exclude Jira issues that have more recent worklogs.

In regards to your query of

worklogDate not in (-1d, now())

I think that would work as well.  However again, there is the possibility that you have issues in Jira that have no worklogs at all.  In which case, neither that query or the AND paired query will return those issues.  Which is why I suggested using the

timespent is empty

parameter as a means to find all the issues in Jira that do not have any time logged on them at all.

I hope this helps, please let me know if I can clarify any other concerns here.

Andy

Suggest an answer

Log in or Sign up to answer