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

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? 

1 answer

1 vote

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

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

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

 

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 Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,317 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot