How to do some flow status filtering on the number of issues created this week with the help of EazyBi

Questions & Answers
1

Hi, guys.
I am planning to implement the following business scenario with EazyBi:

For example, among the number of issues created or closed this week/last week/last month/all, I want to filter out the information that meets the following requirements

  1. Issue status has only ever flowed “Escalate to L2”.
    2、issue state has been transferred to “Escalate to L2” “Escalate to L3”.
    3、Issue status has not been transferred “Escalate to L2” “Escalate to L3”.

I find that in custom formulas, I can never configure them accurately, can anyone assist me with this? Thanks.

2

Hi @xiaofeng

You can define new calculated measures in the Measures dimension with the following formulas:

Issues that have moved to L2, but have not moved to L3 status:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    (
      [Measures].[Transitions to status],
      [Transition Status].[Escalate to L2]
    ) > 0
  ),
  CASE WHEN
  CoalesceEmpty((
    [Measures].[Transitions to status],
    [Transition Status].[Escalate to L3]
  ),0) = 0
  THEN
  1
  END
)

Issues that have moved to both L2 and L3 statuses:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    (
      [Measures].[Transitions to status],
      [Transition Status].[Escalate to L2]
    ) > 0
  ),
  CASE WHEN
  (
    [Measures].[Transitions to status],
    [Transition Status].[Escalate to L3]
  ) > 0
  THEN
  1
  END
)

And for the 3) option, by which date would you like these issues included in the report? Should it show issues created in the selected time period or issues resolved or just open issues?

​Best regards,

​Nauris

3

Hi Nauris，Thanks
I think this formula does not fulfill my business needs.
Because I have a prerequisite requirement, the issue created in this week, based on which I want to filter out the number of issues that have been upgraded to L2, or upgraded to L2 or L3, or have not been upgraded to L2 or L3, respectively.

4

Hi @xiaofeng

To get the issues that are also created in the same selected Time period, you can add another condition to the Filter formula: [Measures].[Issues created] > 0

The formula for L2 would then look like this:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Issues created] > 0
    AND
    (
      [Measures].[Transitions to status],
      [Transition Status].[Escalate to L2]
    ) > 0
  ),
  CASE WHEN
  CoalesceEmpty((
    [Measures].[Transitions to status],
    [Transition Status].[Escalate to L3]
  ),0) = 0
  THEN
  1
  END
)

For the third measure you can then use the following formula:

[Measures].[Issues created] 
- 
([Measures].[nameOfTheFirstL2Measure] + [Measures].[nameOfTheSecondL2L3Measure])

Nauris

5

Thank you very much. I’m trying to verify it this way.