I have been asked to calculate the number of days a date has changed and show that information in a field. I have the calculated field option bug I am not sure how I translate that into this type of thing. In other words, if someone changes a goal date by 2 weeks later, it should show a -14. If someone changes it so that it two weeks earlier it should show 14. Anyone know if there is a way to do this?
1.7 the free one. I haven't upgraded because after that you have to pay for it.
I understand. However, JMCF 2 includes a much more powerful scripting language (Groovy) and simplified API. Coding what you're looking for in JMCF 1.7 is much harder because it requires using the native Jira Java API which is... complicated.
I assume you want to show the number of days a Date field was _last_ changed, right? In other words, in case the date field was modified multiple times, you only want the last modification?
Correct, right now I have kind of a solution in that I can compare between two custom field dates and come back with a value but what they are looking for is really when one date itself changes. The problem is I can't see how to compare a date against itself and when it changed. What I am being told is what date changed in the last week and what that delta is (positive or negative).
Also, i am not against purchasing yet another plugin if necessary, but if I can do it with the free version I would prefer that.
The solution is to look at the field's history. In JMCF 2, you'd do something like:
def history = issue.getFieldHistory("Custom Date Picker")
if (!history.size())
return null;
def lastChange = history.last()
if (!lastChange.from || !lastChange.to)
return null;
Date.parse("yyyy-MM-dd",lastChange.from) - Date.parse("yyyy-MM-dd",lastChange.to)
I can't provide the equivalent for JMCF 1.7 off the top of my head though, but it should be possible.
This seems to be looking for a specific date. Is there a way to have it always looking to see if a change occurred in the last 7 days and then returning that value?
What do you mean by "looking for a specific"? It just looks for the latest change of the custom date picker field.
If you want to ignore changes made more than 7 days ago, you can do the following:
def history = issue.getFieldHistory("Custom Date Picker")
if (!history.size())
return null;
def lastChange = history.last()
if (!lastChange.from || !lastChange.to || lastChange.created.before(new Date() - 7))
return null;
Date.parse("yyyy-MM-dd",lastChange.from) - Date.parse("yyyy-MM-dd",lastChange.to)
Thanks David. The field is rendered bug it still appears to show the information even after it passed the set time. I had a date I changed 3 days ago so I changed the -7 to -2 and it is still showing up.
And there was no change to the field after that? It's easy to see on the History tab of the issue.
It's working. It was just off by a day on my side. Thanks for your help.
David, I hope you don't mind helping me tweak this one more time. We actually need to be able to do this with two separate fields because doing it with one field is going to cause some challenges. We have two fields. The first is original due date and the second is changed due date. We would like to still detect if the changed due date is set for the first time, or has changed in the last week, but we want to compare it against the original due date if the changed due date is set for the first time. If it is set more than one time then we want to do what you showed above against the date history. Does this make sense? Part of the reason for this is because we have to be able to change the original due date in some instances.
Hi Joe,
I'm afraid I didn't get that. Can you rephrase and provide examples?
Yeah, it is a little complicated. In our current setup we have an "original due date" for an item. Once that due date is set, it isn't really supposed to change. Instead we have a "changed due date" that represents any changes that are being made to an end date. Then we have a calculated field called "goal date" that will represent the "original due date" if the "changed due date" is empty. If the "changed due date" is filled out then the "goal date" will be equal to the "changed due date". We needed to do the calculations we discussed above and I thought the best option would be to do that against a single date rather than comparing two. So we had setup so that when the date is first set then it becomes an "original due date" calculated field that pulls the first value that is set. That is all working thanks to your help. Then we would only do evaluation on one single date and it's history. The problem is that we found some legitimate use cases where we couldn't have the original due date be a read only calculated field. It will need to be something that can be set manually. This means that the above calculation won't always be against the history of one value.
If the original due date is set but changed due date is not set then the calculated value is null or blank.
If the original due date is set and the changed due date is being set only for the first time then I need the above calculation to be between the original due date and the changed due date over the last 7 days.
If the changed due date is changed more than once then the we need the above formula comparing it against it's own history.
I don't know if that is possible or if that make sense. I had considered doing a copy of original due date to changed due date the first time it is set so that the formula would still only be calculated off of one field but the original due date needs to be able to be manually set and changed as well.
I'm afraid you lost me.
Your original request was to know by how much a single date field was last changed, only if that change was done in the last 7 days. And that's what the formula I provided does.
I assume you applied that formula to the "changed due date" field.
But how is the "original due date" supposed to come into play? You wrote "If the original due date is set and the changed due date is being set only for the first time then I need the above calculation to be between the original due date and the changed due date over the last 7 days.". But the calculation is not between dates, it's just between the current value and the previous value.
Can you describe your need not in terms of "the above calculation" but in terms of a functional requirement?
What we are trying to do is that we need a weekly calculation of how much a goal date has changed. That is the above formula. We do profit loss and gain calculations based on the number of days something changed using another calculated field, but it is evaluated on a weekly basis. Thus the 7 days in the formula above. This all works when it is one field "changed due date". However the initial time it is calculated is based on the "original due date" to the first time the "changed due date" is set. After that it is based on whether or not changed due date was changed again. The reason I needed to still have the original due date is because it can be changed.
I may still not be being very clear. What we are trying to do is have a calculated field that determines if a goal date has changed in the last 7 day, and if so, by how much. For example, if the goal changes by 30 days later within the last 7 days then it would return a value of -30. If the date is brought in by 30 days then it would have a value of 30. This works well with your above example of it is only using one date to compare with it's own history. The issue is that we have an original due date that is being set and then later on a changed due date being set. The first time the changed due date is being set we need to calculate this field as the change between the original due date and the changed due date. If the changed due date is changed a second or third time, etc, we need it just calculated between the previous changed due date and the new changed due date. But only if it changed within the last 7 days. Hope that helps explain it.
Hi Joe,
you are actually very clear and I believe I understand your requirement. However, I'll need a little time to update the script.
I believe this is what you need:
def history = issue.getFieldHistory("changed due date")
if (!history.size())
return null; //changed due date is still empty
def lastChange = history.last()
if (!lastChange.from && lastChange.created.after(new Date() - 7) && issue.get("original due date")) //this is the first time the changed due date is set
return issue.get("original due date") - Date.parse("yyyy-MM-dd",lastChange.to);
if (!lastChange.from || !lastChange.to || lastChange.created.before(new Date() - 7))
return null;
return Date.parse("yyyy-MM-dd",lastChange.from) - Date.parse("yyyy-MM-dd",lastChange.to);
Thanks... I'll try it out. BTW, I noticed that the -7 days doesn't seem to kick in until the next day has passed. Any way to get it to show also on the day it changed?
What do you mean? It should pick up any change that happened over the last 7 days (current day included)
It's working. I think I had a something wrong or misconfigured on my staging server. Thanks for your help. BTW, I am using the calculated number field and noticed that there was an calculated html field that said it would take html formatting. Does it allow for colors? Just wondering if I can change the color of the test using html based on a negative (red) or positive (green) value.
You can actually use the Calculated Number field's Format Expression option to add conditional color to the value.
To you have a color example you could point me to? I don't see it in the doc. At least the documentation I am looking at.
One bit of an issue is that the nice formatting we get in these fields, don't translate when we are pulling these into reports on Confluence.
Yes, unfortunately, the Jira macro in Confluence doesn't support custom formatting - it formats data on its own.
Since the Format Expression returns HTML, you can use a <span> tag for example:
if (value >=0)
return "<span style='color:green'>" + numberTool.format(value) + "</span>"
else
return "<span style='color:red'>" + numberTool.format(value) + "</span>"
David, I just wanted to thank you for all of your help. It is really appreciated.
You're quite welcome!