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

[Jerome Renaud]

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. 

[Lauma Cīrule]

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 

[Jerome Renaud]

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.

[Lauma Cīrule]

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

[Jerome Renaud]

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

[Lauma Cīrule]

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