Structure for Jira: How to take 2 aggregated functions and get a distinct count in a column

[Colin Crasto]

We use Structure for Jira 5.6

Our goal is to find the total number of distinct Developers and Code Reviewers in the same column.

We have managed to use join#subtree#distinct{reviewer} and join#subtree#distinct{developer}

The above gives us the sample output below

1. Jack , Tom , John , Deo

2. Tom , Harry , John

What we want is a column formula to show us the distinct count which is 5

[Fernando Bordallo]

Hello @Colin Crasto

I wonder if you could leverage local variables to hold the two distinct subsets and create a third distinct output from the join of the previous two:

WITH subset_1 = join#subtree#distinct{reviewer} :

WITH subset_2 = join#subtree#distinct{developer} :

-> Join local subsets in a distinct output

[Colin Crasto]

Thanks for your reply Fernando. Unfortunately the Structure Expression language is quite limited in what we can do with the variables subset_1 and subset_2.

I dont see any string manipulation functions that would help us take 2 lists of Strings , find the distinct  and then have a count on that list.

https://wiki.almworks.com/display/structure/Expr+Language+Reference

https://wiki.almworks.com/display/structure/Expr+Function+Reference

Is there a way to post this question to the Structure for JIRA developers @AlM Works ?

[Fernando Bordallo]

They have a section in their Confluence space just for this sort of thing :)
https://wiki.almworks.com/display/structure/Send+Feedback

[Colin Crasto]

Yes I see that thanks. But then the possible solution would get buried in emails etc.

Is there a way to tag them here so other people can also benefit ?

[Eugene Sokhransky _ALM Works_]

Hi Colin,

Thanks a lot for your question. We try to monitor the community the best we can, but sometimes requests fall through. One of my colleagues will get back to you shortly here or you can always shoot us an email at support@almworks.com

Regards,

Eugene

[Colin Crasto]

Thanks @Eugene Sokhransky _ALM Works_ 

[Egor Tasa [ALM Works]]

Hi Colin,

We are planning to introduce lists/arrays handling in formulas, but until then here is a solution (there may be something better, but I could not think of such):

with subset=JOIN#subtree#distinct{reviewer}:
LEN(REPLACE(subset,"/[^,]/"))+1

Basically, it removes everything but commas, calculates length of resulting string (number of commas) and adds 1. Hopefully you do not need to check for zero reviewers or developers, but if you need, you would have to add IF clause there:

IF (LEN(subset)=0;0;LEN(REPLACE(subset,"/[^,]/"))+1)

Please, let me know if it helps.

Regards,
Egor Tasa

ALM Works

[Colin Crasto]

@Egor Tasa [ALM Works]  thanks for the suggestion.

This is what we tried.

with developers=JOIN#subtree#distinct{developer}:

with reviewers=JOIN#subtree#distinct{reviewer}:

LEN(REPLACE(developers,"/[^,]/"))+1 + LEN(REPLACE(reviewers,"/[^,]/"))+1

However we are still stuck with the distinct set of developers and reviewers.

Its great to know you are considering lists/arrays. Do you have an ETA for this feature.

[Egor Tasa [ALM Works]]

Oh, sorry, Colin,

I did not realize that you needed a distinct list out of both lists. That would be tricky at the moment if at all possible - potentially you can find first value in first list and replace-remove it from the second, then find the second value, but you'd have to do it in iterations as there is no cycle operators - it will be a huge and slow formula. The formula improvements are under development, but I cannot say when they will be ready, as there is a lot to do in that direction.

Regards,
Egor

[Colin Crasto]

Cheers thanks @Egor Tasa [ALM Works]