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
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
Hi, when I tried to use the code provided, it did not generate valid data directly, please see the attached image.
Looking forward to your reply.
Please select the format “integer” for your calculated measure.
https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members#Calculatedmeasuresandmembers-Formatting
Martins / eazyBI support
This is a jql statement, how can this fetch result be implemented within EazyBI?
status = “Waiting for customer”
and reporter not in membersOf(“jira-servicedesk-users”)
You can try creating a report with “Status” dimension as page filter and the import the “Reporter group” dimension filter.
Next, create the new calculated measure using this formula:
Nonzero((
[Measures].[Issues created]
)
-
(
[Measures].[Issues created],
[Reporter Group].[jira-servicedesk-users]
)
)
It should calculate the issues created by other reporter groups but jira-service-desk users.
Martins / eazyBI
Thank you very much, I know I can achieve status through filters, but if I can completely create a custom field containing two contents how can I achieve it?
Try using custom javascript code in advanced settings to define a completely new calculated field and then import the field as a new dimension.
https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-custom-fields/javascript-calculated-custom-fields
Martins / eazyBI
Ok, thanks, so what do I need to do if I customize the indicator through a formula?
@xiaofeng
There is no way to build a completely new dimension from the MDX.
Perhaps you can share more details on what exactly do you want to achieve in your report (what dimensions would you use in the report rows/dimensions/page filters).
Martins / eazyBI
Hi, I have a reporting scenario right now and I’m not sure how to optimize it.
I have work orders with corresponding workflows like Escalate to L2, L3
I want to count the number of workflows that have flowed to L2 over a certain period of time, such as this week, last week, last month, etc.
Simply put, this is a count of the number of people who have been upgraded to L2 or L3 in a week.
Does one issue represent one person in your project?
You mention “number of workflows” and later also “number of people”? is that the same granularity for the report?
Martins / eazyBI
Sorry, I mischaracterized it.
What I’m trying to achieve is to filter out the number of issues whose status flows to L2 or has ever flowed to L2 and L3.
Try these codes:
issues that transitioned to L2 in particulare report period and ever transitioned to L3
Sum(
DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
CASE WHEN
(
[Measures].[Transitions to status],
[Transition Status].[Escalate to L2]
)>0
AND
(
[Measures].[Transitions to status],
[Time].Currenthierarchy.Defaultmember,
[Transition Status].[Escalate to L3]
)>0
THEN
1
END
)
And
issues that transitioned to L2 in particular period but never to L3
Sum(
DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
CASE WHEN
(
[Measures].[Transitions to status],
[Transition Status].[Escalate to L2]
)>0
AND
IsEmpty((
[Measures].[Transitions to status],
[Time].Currenthierarchy.Defaultmember,
[Transition Status].[Escalate to L3]
))
THEN
1
END
)
Martins
Thank you very much for your reply.
I have two more questions though
the number of issues that are screened out without ever transitioning to L2 and L3
followed by the owner of the three scenarios, I expect the date of screening, is the time of creation of the issue, such as I screen the creation of this week and only flowed to L2 not ever flowed to L3