Structure.gantt - create a formula to search for a specific text in a string

[Martin Härri]

We have a field (probably custom) called "Planned Version", which we use to indicate in which increment an epic is planned (same values behind as for field "Fix Version"). This field can have multiple values when we did not manage to complete an epic, it will roll over to the next increment. So it might be "Increment 4, Increment 5, Increment 6". I would now like to build a formula column which searches for a specific increment in this text and sets the start date for a bar in the gantt accordingly. E.g. if the string contains Increment 4 it was first of Jan 2023, if it contains Increment 5 it was first of april, if it contains Increment 6 it was 1st of June. I am trying to find the Expr syntax for that, but have not been successful so far.

Any hint would be appreciated. Thanks!

[David Niro · Accepted solution]

Hello @Martin Härri ,

From what you describe, it sounds like CASE() would be the best option here.  

Please let me know if this helps!

Best,
David

[Martin Härri]

Hi David

Thanks a lot for that hint! I tried the following formula 

case(plannedversion;"Increment 23";date("2022-08-31");"")

but it does not work yet. Any suggestions?

[David Niro]

Hi @Martin Härri ,

You're very welcome!  What do you mean by it doesn't work?  Do you get an error, or the dates you expect to see don't appear?

I've just done a modified version in a gantt chart where I look for specific Summaries and it works fine.

It may be that plannedversion is not being mapped correctly (check your variable list) or that the values don't match the actual options.

Best,
David

[David Niro]

Hello @Martin Härri ,

I overlooked the part about the field having multiple values.  Is it safe to say that the LAST increment is going to be the one you want to use for the date? 

If so, you could try this:

with _PV = plannedversion.LAST():

CASE(_PV;"Increment 23";date("2022-08-31");"")

It will filter out only the last planned version and then use that value in the CASE().  CASE() does not work if the field has multiple values.

Let me know if it helps!

Best,
David

[Martin Härri]

Brilliant, thanks a lot, that works and the formula is now more elegant!
Just one last question: I have some issues which have no planned version yet, and in this case I would like to have no start date at all, i.e. no bar in the gantt chart. Is there an "EMPTY" or "NA()" value in Expr?

[David Niro]

Hi @Martin Härri ,

You're very welcome!!  Glad to hear it is working for you now!!!

Regarding the issues with no planned version yet, there is no way to hide them.  That being said, they should default to Auto Schedule mode.  

There is more on the topic in the link, but the general idea is that the start date will be the start of the project (per gantt configuration) or some other date defined by the issue's dependency to another.

Hope this helps.

Best,
David

[Martin Härri · Accepted solution]

Hi David - right now I have the formula if(search("Increment 23";plannedversion)>0;date("2022-08-31");if(search("Increment 24";plannedversion)>0;date("2022-10-30");if(search("Increment 25";plannedversion)>0;date("2023-03-01");if(search("Increment 26";plannedversion)>0;date("2023-05-31");if(search("Increment 27";plannedversion)>0;date("2023-08-30");if(search("Increment 28";plannedversion)>0;date("2023-11-06");""))))))

for the start date, and it works, all dates are correctly set.

I would like to replace it with the "case" syntax that you proposed (much more elegant), but if I add

case(plannedversion;"Increment 23";date("2022-08-31");"")

I don't see any start dates anymore, even for those issues which have "Increment 23" in the Planned Version/s field