[JIRA 6/ eazy BI] about 'Aggregate' function

Hi all,

I've been using the "Aggregate" function for a while (without understanding how the "numeric expression" works and has to be used. From documentation it is said that "If a numeric expression is provided, this function first valuates and then sums the numeric expression for each cell in the specified set. ").

What I'm trying to do to ventilate some project time spent like this

Project "Working for BU truck" =

Aggregate({

[project][a],

[project][b]/3,

[project][c]/2

})

Of course this not works and I understand that the expression is a bit odd. It want to says that the virtual project "Working for BU truck" is made of 100% of values from "project a", 33% values of "project b" (traversal project that contributes to all business units) and 50% of "project c".

How can I do that?

Thanks for your help. 

2 answers

Hi Jerome!

In which dimension are you aggregating the Projects? 

To create virtual project that consists of other projects you would need to create an aggregated member in Projects dimension without any numeric calculation 

Aggregate({
  [Project].[a],
  [Project].[b],
  [Project].[c]
})

Then you can add a Measure that returns the custom % based on Project names.

CASE [Project].CurrentMember.Name 
  WHEN "Working for BU truck" THEN 1
  WHEN "b" THEN 0.33
  WHEN "c" THEN 0.5
END

Note that 1 is 100% when formatting of calculated member is set to Integer % or Decimal %. See example here https://eazybi.com/accounts/1632/cubes/Issues#report/Test%3A%20custom%20%25%20values%20from%20Project%20name.

Let me know if you have additional questions regarding this!
Lauma / support@eazybi.com 

Hi Lauma, Well, I know that what I'm trying to do might be a bit complicated to explain. In fact I have a dimension which is "project code". Some projects code can be directly imputed to a specific BU while some other (like R&D projects or technical maintenance) has to be dispatched linearly across all BUs. What I did is just "add" projects. It's ugly in concept (only measures can be summed up) but it seams to works has expected. I hope this clarify my need. Thanks again for your help Lauma! J.

Hi Jerome, I am not sure I fully understood though - are you trying to create a Project or Project code that does not exist in JIRA directly in eazyBI? It might be helpful if you could show me a screenshot of the report currently and highlight what is missing. Lauma

Hi Lauma,
Well, yes it's a virtual "project code" since it's a group of other existing projects. 

Well, I want to see time spent divided by BU:

SC1.PNG

But I have, for example, R&D projects and other things in my BU X aggregation:

SC2.PNG

Direct "project code" or other aggregations. I also had to define a "R&d" aggregation with R&D Project codes:

SC4.PNG 

And then I want to dispatch a third of my R&D costs by BU I do something like that:

SC3.PNG

 

I hope this help you to understand!

Regards,

Jerome

 

I am not quite sure why you are dividing the R&D projects with 3? The returned result of [Novacom Project].[R&D projects] is the set of those projects. When you select this calculated member on rows and, e.g. Days spent on columns you will see total days spent on projects in this set. If you expand it, you will see individual projects. If you wish to do some calculation of measures for these projects then you would add new calculated member in Measures with formula to, for example, divide the Days spent [Measures].[Days spent] / 3 Kind regards, Lauma

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,237 views 14 19
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot