We recently added a new SLA to a project, but it doesn’t apply to closed/resolved tickets, so we are trying to calculate it in EazyBI, here’s what I’ve got now, kindly need your help.
Column "[tt] First Response (d)"
It displays the minimum value of the set “(ticket first gets assigned time), (status transitioned to In Progress time), (Status transitioned to Canceled time)”, it works well
Column "[tt] Met (<1d)"
Same concept as the built-in member “(SLA) - Met”, I want it to show:
1: for First Response day <= 1 day
Empty: for First Response day > 1 day
Till now, it’s working nicely, but if you look at the top 2 rows “All Issues” and “People and Culture Hub - Test”, they show nothing, I can’t find a way to make it just like the built-in member “Time to resolution - Met” to show the subtotal (for example in this case, they should both show 2 instead of nothing).
Can you please help with that? also, we need the formula for the Met % as well, thank you!
NonZero(
Avg(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]), [Measures].[Issues created] > 0
),
CASE WHEN
IsEmpty(CASE WHEN
DateDiffMinutes(
[Issue].CurrentHierarchyMember.get('Created at'), TimestampToDate([Measures].[Transition from status first timestamp])
) <
DateDiffMinutes(
[Issue].CurrentHierarchyMember.get('Created at'),
TimestampToDate(
([Transition field].[Assignee],
[Measures].[Transition to first timestamp],
[Time].CurrentHierarchy.DefaultMember)
)
)
THEN
DateDiffWorkdays(
[Issue].CurrentHierarchyMember.get('Created at'), TimestampToDate([Measures].[Transition from status first timestamp])
)
ELSE
DateDiffWorkdays(
[Issue].CurrentHierarchyMember.get('Created at'),
TimestampToDate(
([Transition field].[Assignee],
[Measures].[Transition to first timestamp],
[Time].CurrentHierarchy.DefaultMember)
)
)
END)
THEN
DateDiffWorkdays([Issue].CurrentHierarchyMember.get('Created at'), now())
ELSE
CASE WHEN
DateDiffMinutes(
[Issue].CurrentHierarchyMember.get('Created at'), TimestampToDate([Measures].[Transition from status first timestamp])
) <
DateDiffMinutes(
[Issue].CurrentHierarchyMember.get('Created at'),
TimestampToDate(
([Transition field].[Assignee],
[Measures].[Transition to first timestamp],
[Time].CurrentHierarchy.DefaultMember)
)
)
THEN
DateDiffWorkdays(
[Issue].CurrentHierarchyMember.get('Created at'), TimestampToDate([Measures].[Transition from status first timestamp])
)
ELSE
DateDiffWorkdays(
[Issue].CurrentHierarchyMember.get('Created at'),
TimestampToDate(
([Transition field].[Assignee],
[Measures].[Transition to first timestamp],
[Time].CurrentHierarchy.DefaultMember)
)
)
END
END
)
)
Hi @hsu1 ,
First, you should add Cache() for your formula “[tt] First Response (d)” as it can be useful when the same calculation is repeated in the report more than once.
And then reuse it in the second formula in which you need to have again Descendants() with “Issue” dimension.