Eazybi Transition to first timestamp problem when changing priority

[Ioannis Kouris]

Hello,

I have created a measure in eazyBI called Average MTTE. This measure is the time difference between the ticket creation date & another measure i have created called Escalate to Region Timestamp. 

Below you can see the formula for the 2 calculated measures i mentioned above:

Average MTTE

NonZero(Avg(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember
)
),
CASE WHEN
([Measures].[Issues escalated],
[Time].CurrentHierarchy.DefaultMember) > 0
THEN
DateDiffMinutes(
[Measures].[Issue created date],
[Measures].[Escalate to Region Timestamp]
)
END
))

---

Escalate to Region Timestamp

TimestampToDate(
(
[Measures].[Transition to first timestamp],
[Transition Status].[With RSOC]
)
)

---

The problem i am dealing with is that there is some kind of reset to the Escalate to Region Timestamp measure when we change the ticket priority.

In the example below, the ticket was high priority when it was created and immediately changed to Medium. Then we transitioned the ticket to status With RSOC and we get AVG MTTE 4h17m.

After that the priority was changed to Low and then it was transitioned to status With RSOC again and the problem appears. We have another entry for AVG MTTE because as it looks like there is a new Escalate to Region Timestamp which is the same time with the time the ticket was transitioned to status With RSOC after the priority change.

So at my final report of AVG MTTE for all issues filtered by priority, the ticket above is calculated twice and we don't want that. We want to calculate only the actual first time the ticket was transitioned to status With RSOC when it had Medium priority.

Is there a way we can achieve that?

Thank you,

[Oskars Laganovskis]

Hello @Ioannis Kouris ,

The "Transition to first timestamp" measure takes into account the full "historic context" - including the Priority.

If you want to only retrieve the first timestamp regardless of the issue priority or other dimension, you need to reset the priority (and other dimension).

The expression for the "first escalation timestamp" might be as follows.

([Measures].[Transition to first timestamp],
 [Transition Status].[With RSOC],
--reset the time dimension
 [Time].CurrentHierarchy.DefaultMember,
--reset the priority
 [Priority].DefaultMember)

Then, you might rewrite the MTTE expression so that it drops the issues of the second or further escalations.

The expression might be as follows.

NonZero(
 Avg(
--set of issues
  Filter(
   Descendants(
    [Issue].CurrentMember,
    [Issue].[Issue]),
--issues created in period
  DateInPeriod(
   [Measures].[Issue created date],
   [Time].CurrentHierarchyMember)
  ),
--value for average
  CASE WHEN
--primary condition - issues were escalated
   ([Measures].[Issues escalated],
    [Time].CurrentHierarchy.DefaultMember) > 0
  THEN
   CASE WHEN
--secondary condition - this escalation was indeed the first escalation  
--retrieve the timestamp for current context escalation
    ([Measures].[Transition to first timestamp],
     [Transition Status].[With RSOC])
     =
--retrieve the ultimately first escalation
    ([Measures].[Transition to first timestamp],
     [Transition Status].[With RSOC],
    --reset the time dimension
     [Time].CurrentHierarchy.DefaultMember,
    --reset the priority
     [Priority].DefaultMember)     
   THEN
--the time until the escalation
    DateDiffMinutes(
     [Measures].[Issue created date],
--the current context escalation timestamp
     TimestampToDate(
      ([Measures].[Transition to first timestamp],
       [Transition Status].[With RSOC])
       )
     )
   END
 END
))

Regards,
Oskars / eazyBI support