SLA vs Number of days in status

Questions & Answers
#1

Hi,

According to the SLA, the case should be closed within 14 working days. However, the time when the case is on the client’s side does not count.

I count it like this:

NonZero(
Count(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
Not IsEmpty([Measures].[Issue resolution date])
AND
[Measures].[number of cases]>0
AND
(Datediffworkdays(
[Measures].[Issue created date],
[Measures].[Issue resolution date])

[Measures].[number of workdays in the status on the “client side”]
)<14
)
)
)

Number of workdays in the status on the “client side”
([Measures].[Workdays in transition status],
[Transition Status].[client side])

The problem is when the case is closed at the turn of the month.

When I choose December, only days on the “client side” status from December are shown, although in December and January there were 9 days in total for case ZOP-14290.

image
image1520×373 29 KB

In the SLA statistics, I show all cases created in a given month, e.g. in December, even if they were closed in the following month. So when I choose December I should have seen all the days in “client side” status when the case was open, not just in December.

Days in a status between specific dates
Days in a status between specific dates
#2

Cache( NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
[Measures].[number of workdays in the status on the “client side”]
))
+ [Measures].[number of workdays in the status on the “client side”]
)

1 Like
#3

OMG thank you!! It’s what I needed. :laughing:

One more question: Is there “futureperiods” instead of “previousperiods”?
Because I need right number of days in December not January.
The raport shows issues created in December even if they are closed in January. Maybe I should change that…

#4

Cache(
NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
[Measures].[number of workdays in the status on the “client side”]
)
+
Sum([Time].CurrentHierarchyMember.DateMembersBetween
([Time].CurrentHierarchyMember.StartDate, ‘today’),
[Measures].[number of workdays in the status on the “client side”]
))
)

#5

It’s not working… I try to figure out why.

But I feel we 're so close :slight_smile:

#6

@liu.jinming are you still willing to help? :slight_smile:

#7

Sum([Time].AllMembers,[Measures].[number of workdays in the status on the “client side”])

#8

Hi @Kamila,
Can you share by the date you want your measure to be related to the Time dimension - by issue resolution date or created date?

Here is one formula, that you can try, I have commented the lines that either relates measure to the Time dimension or ignores, please give it a try and let me know if this solved your question:

NonZero(
  Count(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      -- related to Time dimension by created date
      DateInPeriod(
        [Measures].[Issue created date],
        [Time].CurrentHierarchyMember
      )
      AND
      Not IsEmpty([Measures].[Issue resolution date])
      AND
      (Datediffworkdays(
      [Measures].[Issue created date],
      [Measures].[Issue resolution date])
      -
      -- Time.DefaultMember ignores the Time dimension when the issue has done the transition, check if the spelling is correct for your status 'Client side'
      ([Measures].[Workdays in transition status],
      [Transition Status].[Client side],
      [Time].DefaultMember)
      )
      <14
    )
  )
)

Best,
Gerda // support@eazyBI.com