Can I use a structure formula name in the formula itself?

Question

I want to have a column in a structure that calculates the amount of work for a given task that will fall in a given time period.

For instance, if I have a task that has 15 story points and starts on June 7 and ends on June  25 (3 weeks) I want it to calculate that the task has 5 story point for the weeks of June 7, June 14, June 21.

I can do this with the following formula:

if(days_between(startdate,duedate) > 0;
ftedays*if(duedate > 0;
max(0,7-max(0,days_between(date("Jun 21, 2021"),startdate)) -
max(0,days_between(duedate,date("Jun 27, 2021")))))/days_between(startdate,duedate)/7)

This gives me exactly what I need, but I need to change the formula for each column to change the dates.  Can Jira read the dates "Jun 21, 2021" and "Jun 27, 2021" from somewhere, perhaps the formula name, so I don't need to change the formula for each column manually?

(`ftedays` above is just a renaming of Story Points)

Thanks,

Luke

Luke Fraser · Answer

Thanks, this was helpful. This does 80% of what I wanted to accomplish, and what I wanted to do is likely impossible.

For completeness, if you have a column called "FTE Days" that is the sum of story points the following divides the story points evenly over the period between start and end dates and gives the sum of those points that fall in the interval:

with column_startdate = "Jun 21, 2021":
with column_enddate = "Jun 26, 2021":
with column_days = 7:

if(days_between(startdate,duedate) > 0;
ftedays*if(duedate > 0; 
max(0,column_days-max(0,1+days_between(date(column_startdate),startdate)) - 
max(0,1+days_between(duedate,date(column_enddate)))))/days_between(startdate,duedate)/column_days)

Philip Heijkoop _ALM Works_ · Answer

Hi @Luke Fraser ,

I'm Phil, I work with Dave. I think the mechanism won't work exactly like you're suggestion, but we can probably achieve the same results in a slightly different manner. Allow me to give you a few options and we can always elaborate or tweak one or all based on your needs.

I assume what you're after is we define a new column somewhere called "work between $date1 and $date2" and then when you create a formula of that type and name it "work between Jun 21 2021 and Jun 27 2021", it reads those dates from your column name and fills it in with the calculation dynamically. That won't work, we will likely need to copy paste some elements for each new column. The crux of each will really depend where you're getting those two dates from.

What Dave was suggesting was a local variable, so that would change your formula into:

with my_desired_startdate = "Jun 21, 2021":
with my_desired_enddate = "Jun 27, 2021":
if(days_between(startdate,duedate) > 0;
ftedays*if(duedate > 0;
max(0,7-max(0,days_between(date(my_desired_startdate),startdate)) -
max(0,days_between(duedate,date(my_desired_enddate)))))/days_between(startdate,duedate)/7)

This way any time you wanted to tweak the formula, you only had to change those values at the top. In this particular case, that only gives you a mild improvement, since you only reference those values once, so it's not a huge update.

Above method makes it easy to create yet another formula column with a different date range. Just copy the same definition and change the dates.

I'm going to assume that you don't want to reference a Jira field, because you're already using fields like startdate and duedate. But for the sake of completeness, you could obviously add a variable like above, but instead of defining it at the top of your formula definition, you could just create a reference to whatever Jira field you are looking to get those dates from.

If you upgrade to our latest version, release last week, you can access a lot more information. I assume June 21st and June 27th in your example aren't random date, but coming from somewhere. The new version will allow you to reference more complex data like sprint start and end dates, fixversion release dates, etc. Giving you something like:

if(days_between(startdate,duedate) > 0;
ftedays*if(duedate > 0;
max(0,7-max(0,days_between(date(sprint.startdate),startdate)) -
max(0,days_between(duedate,date(sprint.finishdate)))))/days_between(startdate,duedate)/7)

Another option with the newest version is to create a user-defined function. Those function definitions however only have that formula definition as the scope where it is recognized (so no sharing of functions just yet).

Final suggestion, would be to make your data references column-based.  I assume this is what you want, but I like to write these out in increasing order of complexity. This is almost exactly as we're doing above, but instead of referencing a variable in the formula definition or using a Jira field, we are going to reference something in the structure. Once again, we have two options. We can reference other formula (columns). So add a column whose formula definition is nothing but

"Jun 21, 2021"

or just put in a variable and in the definition select 'formula' from the options. That will give you a second formula editor to put the definition. Using the array options in the latest version, you can also put both dates you want in a single field and parse them automatically. This gives you the same dates for every row in your structure.

Second option, allows you to change the dates you want to use per row. Structure allows for the addition of a Notes column, and you can reference that in your functions just like any Jira field. These notes column will let you put in any date or two dates. So now you can edit the dates you want to calculate in-line and per row.

I think that covers all the major options. If I've misunderstood your ask or you want to dive into it deeper, please feel free to reach out to us.

Best,

Phil Heijkoop

Lead Solutions Engineer

ALM Works

Dave Rosenlund (Trundl) · Answer

My apologies, @Luke Fraser. I misunderstood.

I guess I'm struggling a bit with "as I move from column to column." Let me ask one of my colleagues to have a look at this thread.

-dave