There are many workflows in the project, and I want to count all issues whose state is completed and whose state has only flowed to the L2 state (the state is actually every node in the workflow)

I’m a novice, and I can’t filter out issues that have been in a certain state. Excuse me, what is the good way?

This is the result of my use of JQL statement filtering, how to implement in EazyBI?

【issuetype = Support AND

status in (“Closed”,“Resolved”,“Canceled”)

And status was in (“ESCALATE TO L2”)

And status was not in (“ESCALATE TO L3”,“HANDOVER TO PREMIUM SERVICE”)】

Hey @xiaofeng

Welcome to the eazyBI community.
In this case, you would need a complex formulas to calculate the results based on current and historical statuses.


Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    [Measures].[Issue Type] = "Support"
    AND
    (
      [Measures].[Issue status] = "Closed"
      OR 
      [Measures].[Issue status] = "Resolved"
      OR 
      [Measures].[Issue status] = "Canceled"   
    )
  ),
  CASE WHEN
  (
    [Measures].[transitions to status],
    [Transition Status].[Escalate to L2]
  )>0
  AND
  IsEmpty(
    Sum(
      {
        [Transition Status].[Escalate to L3],
        [Transition Status].[Handover to Premium Service]
      },
      [Measures].[Transitions to status]
    )
  )
  THEN
  1
  END
)

If you find this calculation slow or failing with timeout error, please export and share the definition of your report.
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports/export-and-import-report-definitions

Martins / eazyBI

Sum(
Filter(
DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Issue Type] = “Support”
AND
(
[Measures].[Issue status] = “Closed”
OR
[Measures].[Issue status] = “Resolved”
OR
[Measures].[Issue status] = “Canceled”
)
),
(
[Measures].[transitions to status],
[Transition Status].[HANDOVER TO PREMIUM SERVICE]
)>0

)

For example, if this is the case, please ask me what I can use to make the association.

issuetype = Support

AND status was not in (“ESCALATE TO L2”,“ESCALATE TO L3”,“HANDOVER TO PREMIUM SERVICE”)

AND status = “Waiting for support”

Again, I modified the function according to what you wrote earlier, but it still didn’t work, so it seems I still haven’t found the trick.

In your last query try this formula:

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    [Measures].[Issue Type] = "Support"
    AND
    [Measures].[Issue status] = "Waiting for support" --precise name as in Jira
    AND
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrenthierarchyMember
      )
  ),
  CASE WHEN
  IsEmpty(
    Sum(
      {
        [Transition Status].[Escalate to L2],
        [Transition Status].[Escalate to L3],
        [Transition Status].[Handover to Premium Service]
      },
      [Measures].[Transitions to status]
    )
  )
  AND
  [Measures].[Issues created]>0
  THEN
  1
  END
)

Please check the status name for “Waiting for status” in Jira and use the precise name in the filter expressions as it is a case-sensitive expression

Martins