Calculated Member for Status

[Michael Foley]

Hello,

In EazyBi, I'm trying to create Calculated Members for two things based on certain statuses: open issues and closed issues.

I've created the following for closed statuses and this is working as I would like:

Aggregate({
[Status].[Closed],
[Status].[Resolved],
[Status].[Complete]
}
)

I would like to now create a new Calculated Member that shows all statuses minus the three mentioned above. I've tried something simple utilizing my already created Calculated Member:

([Status].[All Statuses]- [Status].[Closed Status]
)

This gives me the expected results, however, it's displaying Projects will 0 results in addition to the Projects I would expect, even when I have the Hide Blank Rows optioned enabled.

Does anyone know how I can optimize my second Calculated Member to exclude just those three statuses (Closed, Resolved and Complete) and not display NULL Projects?

Thanks!

[Daina Tupule eazyBI]

Hello,

The formula ([Status].[All Statuses]- [Status].[Closed Status]) is simple mathematical subtraction. It calculates particular measure for all issues and then for closed issues. Then uses a simple subtraction between those values and gets a numeric value as a result. 

If all issues in the project are closed then this subtraction will give you 0 as a result. 

You would like to aggregate open status members to retrieve values from issues in open statuses. I would suggest this formula for open status member aggregation:

Aggregate(Except(
 [Status].[Status].Members,
 ChildrenSet([Status].[Closed Status])
))

Daina/support@eazybi.com