Is it possible to add date range to an SLA reporting?

Questions & Answers
1

Hi All,

we have an SLA report, I would to add a date range to the report so that I can filter the SLA report based on month-to-month. Below is my formula.

Sum(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    ([Measures].[Generic resumes submitted in the previous 3 months])>0),
 CASE WHEN
 [Measures].[Avg days to complete resume submission] > 10
 AND
 not isempty ([Location].[Onshore])
THEN
 (([Measures].[Avg days to complete resume submission]-10)*$$)
 WHEN
 [Measures].[Avg days to complete resume submission] > 12
 AND
 NOT IsEmpty ([Location].[Nearshore])
THEN
 (([Measures].[Avg days to complete resume submission]-12)*$$)
 WHEN
 [Measures].[Avg days to complete resume submission] > 12
 AND
 NOT IsEmpty ([Location].[Offshore])
THEN
 (([Measures].[Avg days to complete resume submission]-12)*$$)
END)
2

Hi @agudipelli,

You can filter issues by date in two ways – using issue property (for example “Issue completion date”) or measure that is related to that date (for example, “Issues with completion date”).

In your case, the calculation contains two measures, “Generic resumes submitted in the previous 3 months” and “Avg days to complete resume submission,” and both might affect the outcome of the calculation. You should choose by which one measure you want to relate issues to the selected “Time” dimension in the report – issues submitted in the period or issues completed resume submission.

For example, to see SLA for each period based on the issue submission date, you might want to filter issues that have a submission date in the selected period using the property and then calculate “Avg days to complete resume submission,” ignoring the time period. I would advise to use issue property for checking the Location as well.

Sum(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  --filter issues with submission date in period using issue porperty
  DateInPeriod(
    [Measures].[Issue resume submission date]
    [Time].CurrentHierarchyMember)
  ),
  --calcaulted resume completion ignoring when it is completed
  CASE WHEN
    ([Measures].[Avg days to complete resume submission],
    [Time].CurrentHierarchy.DefaultMember) > 10 AND
   --check issue location using property
    [Measures].[Issue location] = "Onshore"
  THEN
    (
      ([Measures].[Avg days to complete resume submission],
      [Time].CurrentHierarchy.DefaultMember)-10
    )*$$
  WHEN
    ([Measures].[Avg days to complete resume submission],
    [Time].CurrentHierarchy.DefaultMember) > 12 AND
    [Measures].[Issue location] = "Nearshore"
  THEN
    (
      ([Measures].[Avg days to complete resume submission],
      [Time].CurrentHierarchy.DefaultMember)-12
    )*$$
  WHEN 
    ([Measures].[Avg days to complete resume submission],
    [Time].CurrentHierarchy.DefaultMember) > 12 AND
    [Measures].[Issue location] = "Offshore"
  THEN
    (
      ([Measures].[Avg days to complete resume submission],
      [Time].CurrentHierarchy.DefaultMember)-12
    )*$$
  END
)

Another option is to ignore the submission date and check issues that are completed in a period

Sum(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  --filter issues with completed resume submissions using issue porperty
  DateInPeriod(
    [Measures].[Issue completed resume submission date]
    [Time].CurrentHierarchyMember)
  ),
  --calcaulted resume completion
  CASE WHEN
    [Measures].[Avg days to complete resume submission] > 10 AND
    [Measures].[Issue location] = "Onshore"
  THEN
    ([Measures].[Avg days to complete resume submission]-10
    )*100
  WHEN
    [Measures].[Avg days to complete resume submission] > 12 AND
    [Measures].[Issue location] = "Nearshore"
  THEN
    ([Measures].[Avg days to complete resume submission]-12
    )*200
  WHEN 
    [Measures].[Avg days to complete resume submission] > 12 AND
    [Measures].[Issue location] = "Offshore"
  THEN
    ([Measures].[Avg days to complete resume submission]-12
    )*300
  END
)

Note the properties used in the calculations are my guess on how they might look in your Jira. Please adjust the expression to match the real use case.

Kind regards,