[Structure][Formula][History] Trying to have the Storypoint SUM of the backlog project on a D DAY

[agnes BOURLON]

Hi

I had 2 columns to count the story points of a project  backlog

1/ First one counts the "TODAY" story point with the the formula : 

SUM(if(type=EPIC, if( status ="IN PROGRESS" OR status ="DONE", SUM#children{storypoints},storypoints),0))

That permit to count the story points of the US and tasks , children of the EPIC when it is IN PROGRESS or DONE and pick up directly the EPIC story point when the EPIC is in TODO or SUSPENDED.

And I put 0 when the line concerns a US or tasks otherwise I would count twice the points , as they could be already sum at the EPIC level

2/ The second does the same but in the past on a D DAY . Let's say the D DAY is 01/01/2024

I adapted the previous request by changing status or story by the usage of this 

* historical_value(this, "status",datetime("01/01/24 0:00 PM"))

* historical_value(this, "storypoints",datetime("01/01/24 0:00 PM"))

That gives that :

SUM(if(type=EPIC, if (historical_value(this, "status",datetime("01/01/24 0:00 PM")) ="IN PROGRESS" OR historical_value(this, "status",datetime("01/01/24 0:00 PM")) ="DONE",SUM#children{historical_value(this, "storypoints",datetime("01/01/24 0:00 PM"))},historical_value(this, "storypoints",datetime("01/01/24 0:00 PM"))),historical_value(this, "storypoints",datetime("01/01/24 0:00 PM")),0))

---

Results : 

It works pretty well except 3 points

* It is very slow ! I need to wait 2 minutes or more to get the numbers

* I don't have the sum by feature for the column STORY POINTS D DAY. Whereas I got it for the STORY POINTS TODAY. Is it a question of performance ? Please see the red circle below

* I would like to have the substraction between the 2 columns to calculate the gap but without variable usage (I don't find the way to introduce a variable), my structure does a lot of calculation ..... so at this stage, I don't want to have a new column, I prefer to export it in excel and to the substraction there :-(

Could you help me to resolve the 2 last points please

Many thanks in advance,

Agnes

[Stepan Kholodov _Tempo_ · Accepted solution]

Hello @agnes BOURLON 

Although it is possible for the Formula column to take long time to calculate its values - especially if it both accesses history and aggregates values - but it's hard to say if it's expected or not in your case. You can try to optimize the formula a bit to ensure that it reuses calculated values instead of calculating them over and over again:

with status_ = historical_value(this, "status",datetime("01/01/24 0:00 PM")):
with storypoints_ = historical_value(this, "storypoints",datetime("01/01/24 0:00 PM")):

SUM(if(type=EPIC, if (status_ ="IN PROGRESS" OR status_ ="DONE",SUM#children{historical_value(this, "storypoints",datetime("01/01/24 0:00 PM"))},storypoints_),storypoints_,0))

And for rolling up the values on the version level, you can just enable the 'Sum over sub-items' function in the column.

If this doesn't help, please reach out to us via our support portal and we'll have a closer look into this matter.

Best regards,
Stepan
Tempo (the Structure app vendor)

[agnes BOURLON]

Many Thanks, this works wonderfully !