Creating calculated members on IssueType filtering on project?

I want to report  on how my team spends it's time, and segregate this by 'Project Work' vs 'Non-Project' vs 'Support'

So feel I need to add Calculated Members on IssueType. 

 

'Non-Productive' =

(any issue of any type  within Projects named 'Internal 1', 'Internal 2')

OR

(issue types 'Bucket Task' or 'Bug Fix' )

 

All 'Productive' =

Issue not  within a project named 'Internal 1' or 'Internal 2'

AND

issue type not 'Bug Fix' OR 'Support' OR 'Bucket Task' 

 

Support =

Issue not  within a project named 'Internal 1' or 'Internal 2'

AND

issue type = 'Support' 

Then I can build a pie chart using IssueType and Measure (hours spent) as columns, Logged by as a Page and Time as a row.

 

The calculated member for 'Support' is trivial.  However I'm struggling to figure out how to build the other two.

I'd really appreciate some help on this if anyone able to point me in the right direction - having spent about three hours on this trying various combinations of filter/aggregate etc.  Please go easy on me, I'm new to this and have never used a BI reporting tool before - so this is a steep learning curve.

 

Please help!

2 answers

1 accepted

This widget could not be displayed.

Hi Graeme,

 

In this case, we recommend using tuples to create new calculated measures for your needed selections.

Please find example of code that you could try for creating new calculated measure: "Non-Productive hours"

 

Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    DateInPeriod
    ([Issue].CurrentHierarchyMember.get('Created at'), 
    [Time].CurrentHierarchyMember) 
    AND
    [Measures].[Issues created]>0
    AND
    (
      (
      [Issue].CurrentMember.Parent.Name MATCHES '.*Internal 1.*'
        OR 
       [Issue].CurrentMember.Parent.Name MATCHES '.*Internal 2*'
      )
      OR
      (
        ([Issue].CurrentMember,
        [Measures].[Issues created],
        [Issue type].[Bucket Task])>0
        OR
        ([Issue].CurrentMember,
        [Measures].[Issues created],
        [Issue type].[Bug Fix])>0
      )
    )
),[Measures].[Hours spent]
)

 

In a quite similar way, you could create both other calculated measures using NOT MATCHES function and IsEmpty (in tuple cases) for filtering selection that not matching some criteria.

 

Then you could use "Measures" dimension with 3 selected measures in columns, "Logged" dimension by in pages, and Time dimension in rows.

At the end you could get report like attached (with possibility to drill in Time dimension and select user):

 pie chart example.jpg


Please contact support@eazybi.com if you have further questions regarding this!

 

Kind regards,

Martins Vanags

This widget could not be displayed.

Thank you so much Martins.  I really appreciate you input and will give this a try.

 

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted yesterday in Teamwork

What teamwork quotes inspire you?

Hey everyone! My name is Natalie and I'm an editor of the Atlassian Blog and I've got a question for you: What's your favorite quote about teamwork?  We've compiled a list here, along with...

34 views 3 4
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you