All issues except some issue calculated member

Questions & Answers
1

Hello,
I’m beginner with calculated member but try something which should be easy for you I think.
I created 3 small calculated members :
[Issue].[Hardware]=Aggregate([Issue].[CITIX 3D],[Issue].[COMETH])
[Issue].[Infrastructure]=[Issue].[Infrastucture serveur]
[Issue].[Absences]=[Issue].[Tâches récurrentes (Hors Projet)].[TEMPS-3 Arrêt maladie] +
[Issue].[Tâches récurrentes (Hors Projet)].[TEMPS-2 Congés]

I want now create a calculated member for all the other issues :
[Issue].[Others]=Except([Issue].[All Issues],{ [Issue].[Hardware] ,
[Issue].[Infrastructure], [Issue].[Absences] })

What should be the result type of my calculated menber? Isn’t it a Set?

Thanks in advance,

2

Hi @gmeriaux,

I would recommend creating an exception for whole projects in the Project dimension and then adding it to the Pages section and selecting the calculated member. The calculated member could look like this:

Aggregate(Except(
  [Project].[Project].Members,
  {[Project].[CITIX 3D],
  [Project].[COMETH],
  [Project].[Infrastucture serveur]}
))

And create a calculated member in the Issues dimension, to exclude the two individual issues:

Aggregate(
  Except([Issue].[Issue].Members,
    {[Issue].[Tâches récurrentes (Hors Projet)].[TEMPS-3 Arrêt maladie]
    [Issue].[Tâches récurrentes (Hors Projet)].[TEMPS-2 Congés]}
  )
)

If you still want to use the Issue dimension, then please update the calculated members “Hardware” and “Infrastructure” to aggregate the children of each project. This is because you have to use the “Issue” level in the calculated member “Others”, as you also exclude individual issues. The formula would look like this for “Hardware”:

Aggregate({
  [Issue].[CITIX 3D].Children,
  [Issue].[COMETH].Children
})

Also, please use the Aggregate() function for the two members of the calculated member “Absences” or add them to the Except() function individually. Otherwise, the formula for “Others” won’t take them into account.

The formula for the calculated member “Others” would look like this:

Aggregate(
  Except([Issue].[Issue].Members,
    {ChildrenSet([Issue].[Hardware]),
    {ChildrenSet([Issue].[Infrastructure]),
    [Issue].[Tâches récurrentes (Hors Projet)].[TEMPS-3 Arrêt maladie]
    [Issue].[Tâches récurrentes (Hors Projet)].[TEMPS-2 Congés]}
  )
)

Regarding your question about the results of your original calculated members - they are sets (except “Absences”). The reason you didn’t get the expected results in the Except() function is the calculated members had different levels (All, Project, Issue). To create this calculation, all the involved parties must be at the same level, because the function returns the members of the first set you define [Issue].[Issue].Members and removes any members in the second set. If the second sets member levels don’t match with the first set, no members will be removed from the first set.

Please read more about the function Except() on our documentation page - https://docs.eazybi.com/display/EAZYBI/Except.

Kind regards,
Roberts // eazyBI support