eazyBI - Create calculated group of a subset of members in a JIRA group

[Michael Thompson]

Hello all, 

First let me say eazyBI has been a godsend for use for detailed reporting. We're getting a lot of useful data from the reports I've created in only the first week of use.

Currently I am tasked with generating an open issue aging report that can be drilled into from our overall division, to each team (which is from JIRA groups I am importing, and then to the individual employees. Step 1, creating an overall group, was an easy calculated member:

Aggregate(
 Filter([Assignee].[User].Members,
 CoalesceEmpty([Assignee].CurrentMember.get('Groups'), '')
 MATCHES '.*(^|,)(jiraGrpA|jiraGrpB|jiraGrpC)($|,).*'
 )
)

However, while this gives me a nice breakdown I quickly realized that there are several employees that are members of more than one group. This ends up skewing the numbers at overall and team levels, since these people have their open issue counts listed multiple times.

My thought was I could create groups as calculated members, then change the calculated member aggregate listed above to use these calculated members instead of the jira groups. Each calculated member would be an aggregate of all members of a specific group, minus the duplicated member. My problem is that I cannot figure out the proper calculation to do this.

For reference points, let's say my JIRA groups contain the following members.

jiraGrpA = emp1, emp2, emp3

jiraGrpB = emp1, emp4, emp5

jiraGrpC = emp1, emp4, emp6, emp7

[Janis Plume _eazyBI_ · Accepted solution]

Michael,

Unfortunately, the idea with calculated members instead of the groups has a shortage. The reason is that calculated members are independent and they can not see other member and adjust the calculation depending on the other member calculation. So, in your example, emp1 is a duplicate for all the groups, and you need to tell eazyBI for which group it should be excluded. If you wish to count a user for exactly one group, you will end up managing an alternative user/group configuration in eazyBI with the formulas like this:

Aggregate(
Except(
-- set of users filtered by group
Filter([Assignee].[User].Members,
CoalesceEmpty([Assignee].CurrentMember.get('Groups'), '') 
MATCHES '.*(^|,)(jiraGrpc)($|,).*'),
-- set of users to be excluded
{[Assignee].[emp1],
[Assignee].[emp4]}
))

EazyBI considers it as the correct approach when the measure adds the fact for each of the dimension members when there are multiple members of a dimension to which the fact is related. Workarounds for such cases are when you can tell the algorithm how eazyBI can decide which group is "the right" from group names or some other group attribute.

Best regards,

--
Janis Plume
eazyBI support
support@eazybi.com

[Ramaiah Pendli]

we have 2 calculate members groups as below group1, group2 and we need to create others will be other than these to groups. 

group1

Aggregate({
[Assignee].[a1],
[Assignee].[a2],
[Assignee].[a3] })

group2

Aggregate({
[Assignee].[b1],
[Assignee].[b2],
[Assignee].[b3] })

please let me know how to create 3rd group other persons not belongs to above 2 groups.

[Janis Plume _eazyBI_]

Hi,

You can create one more calculated member and exclude from all the members the children of both groups:

Aggregate(
Except(
[Assignee].[User].Members,
{ChildrenSet([Assignee].[group1]),
ChildrenSet([Assignee].[group2])}
)
)

Kindly,
Janis, eazyBI support

[Michael Thompson]

Thank you Janis, I appreciate the detailed response!