We’re using the basic statuses of “TO DO”, “IN PROGRESS”, “DONE”. For reporting, I want to calculate a new status called “DEFERRED”. This would be for issues currently set to either “TO DO” or “IN PROGRESS” and with another condition being true, based on other fields that I have pulled into my cube.
How do I create this new “virtual” status value and how do I subtrack them from the exiting “TO DO” and “IN PROGRESS” counts.
I intend to use these statuses as a Column dimension and report against projects, which will be my Row dimension.
Thanks for any guidance or suggestions!
Hi!
To create groups of statuses you can use Aggregate(…) function in Status dimension / Calculated members. Further you can use this new calculated group of statuses together with Measures dimension on columns - each measure will show value for the current group of statuses.
Let me know if that helped!
Kind regards,
Lauma / support@eazybi.com
Thanks for the reply. I’m looking to create a calculated member, in the Status dimension, that is calculated totally from other fields, specifically, when those two other fields are not equal. Here’s the exact case:
We have custom fields in Jira, let’s call them Field A and Field B. These are single select, based on the same list of available text values. I have imported them into my cube as both dimension and property.
I want to create a calculated member in the Status dimension that will be applied, when these two values are different (regardless of the actual Status). Let’s call this new calculated member “Deferred”.
Additionally, I would like to create additional calculated members in Status, to subtract the Deferred items from their normal status. I assume this is as simple as:
[Status].[New TODO] =
[Status].[TODO] - [Status].[Deferred]
But maybe I need to be more clever, since Deferred values overlap with each of my imported Statuses. How is subtraction handled in dimension calculations like this?
Thanks for your help.
-Seth
1 Like
I tried using this to create my new Calculated Member, but it errors out with the very frustrating “…must not be a set”
[Status].[Deferred] =
Filter(
[Status].Members, [Measures].[Issue Field A] =
[Measures].[Issue Field B])
Hi!
I know that you have been discussing the question regarding the Statuses in a lot more detail with my colleague Daina over e-mail. Please let us know if you have further questions regarding that!
Regarding the error message you get - for each set in MDX there has to be some aggregator of how it should be returned. For the Status dimension calculated members you can use the Aggregate(…) function, in Measures you would use other aggregates as described here.
Kind regards,
Lauma / support@eazybi.com
@daina.tupule has been very helpful, through the support channel. Thanks, Daina!
I was not able to create calculated members, on the Status dimension. Instead, I created calculated measures, in the measures dimension. These ended up looking like this:
[Measures].[Issues Deferred]=
NonZero(
Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
Not isEmpty([Issue].CurrentHierarchyMember.get(‘Field A’))
AND
[Issue].CurrentHierarchyMember.getstring('Field A) <> [Issue].CurrentHierarchyMember.getstring(‘Field B’)
AND
([Measures].[Issues created] > 0)
)
)
)