[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