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

[Luke Fraser]

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

[Dave Rosenlund]

Hi, @Luke Fraser.  You can :)     

You’ll create a variable that you reference in the formula, that itself is another formula.

To do this, you'd write your formula as usual, referencing this variable (not yet defined).

When you get prompted for the variable definition, you scroll down the list of options to formula and a new editor will open in the same window. You can write another formula there.

Give it a shot and let us know how you make out. 

-dave [ALM Works]

[Luke Fraser]

I'm not clear what you are recommending here.  In my original formula I want the dates "Jun 21, 2021" and "Jun 27, 2021" to be replaced by some variable whose value would change based on which column I'm in. 

I'm clear on how to have variable values change as rows change, that's the usual way of doing things.  What I want is to have a formula like the one below, where _period_start_ and _period_end_ have values that change as I move from column to column. 

if(days_between(startdate,duedate) > 0;
ftedays*if(duedate > 0;
max(0,7-max(0,days_between(date(_period_start_),startdate)) -
max(0,days_between(duedate,date(_period_end_)))))/days_between(startdate,duedate)/7)

 Are you suggesting I can do that, and I'm missing it?

[Dave Rosenlund]

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 

[Philip Heijkoop _ALM Works_]

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

[Luke Fraser]

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)