I am trying to create a SLA Report by Month

Questions & Answers
1

Hello

I am trying to create an SLA report that shows by month how many issues were resolved within the SLA. Below is the code that calculates the work hours so that I can calculate whetehr the time met the SLA. The code works when I have Issues in the rows. How can I get this code to work when I have time in Rows

iif(
NonZero(AVG(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember
)
AND
([Measures].[Issues created],
[Time].CurrentHierarchy.Defaultmember) > 0),
– ### workhours calculation on Issue level ###
DateDiffWorkDays(
DateWithoutTime([Measures].[Issue created date]),
DateAddDays(DateWithoutTime([Measures].[Issue resolution date]),1)
)
9-24
(mod(cast(format([Measures].[Issue created date], “###.######”) as NUMERIC),1)
-9/24+18/24-
mod(cast(format([Measures].[Issue resolution date], “###.######”) as NUMERIC),1)
)
))<0,0,
NonZero(AVG(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember
)
AND
([Measures].[Issues created],
[Time].CurrentHierarchy.Defaultmember) > 0),
– ### workhours calculation on Issue level ###
DateDiffWorkDays(
DateWithoutTime([Measures].[Issue created date]),
DateAddDays(DateWithoutTime([Measures].[Issue resolution date]),1)
)
9-24
(mod(cast(format([Measures].[Issue created date], “###.######”) as NUMERIC),1)
-9/24+18/24-
mod(cast(format([Measures].[Issue resolution date], “###.######”) as NUMERIC),1)
)
))
)

2

Hi!

I see a couple of syntax errors in the MDX formula.

  • After DateAddDays(…) functions there are no mathematical signs for 9-24
  • The format function has " while it should use ’

Please try the following

iif(
  NonZero(AVG(
    Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
      )
    AND
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.Defaultmember) > 0),
-- ### workhours calculation on Issue level ###
  DateDiffWorkDays(
    DateWithoutTime([Measures].[Issue created date]),
    DateAddDays(
      DateWithoutTime([Measures].[Issue resolution date]),1)
    )
  -9-24-
  (mod(cast(format(
     [Measures].[Issue created date], '###.######') as NUMERIC),1)
     -9/24+18/24-
   mod(cast(format(
     [Measures].[Issue resolution date], '###.######') as NUMERIC),1)
  )
  ))<0,
0,
  NonZero(AVG(
    Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
      )
    AND
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.Defaultmember) > 0),
-- ### workhours calculation on Issue level ###
  DateDiffWorkDays(
    DateWithoutTime([Measures].[Issue created date]),
    DateAddDays(
      DateWithoutTime([Measures].[Issue resolution date]),1)
    )
  -9-24-
  (mod(cast(format(
     [Measures].[Issue created date], '###.######') as NUMERIC),1)
     -9/24+18/24-
   mod(cast(format(
     [Measures].[Issue resolution date], '###.######') as NUMERIC),1)
  )
  ))
)

After correcting the formula was working for Issue and Time dimensions. I did not further check the logic of the formula though. Let me know if you have further questions.

Lauma / support@eazybi.com