Hello,
I have created a calculated measure using datediff which calculates how many days were between the created and start date as per below image. Anything over 10 days is outside of SLA as is red.
I would now like to create a summary table which simply shows the count of issues that were inside SLA and the count that are outside.
Any help would be appreciated.
Thanks
Carly
Hi @CarlyBooth,
Welcome to the eazyBI community! 
My recommendation is to use custom interval dimension where you can set up intervals as you wish. This will be a faster solution that will have better performance.
The JavaScript that you need to add to eazyBI advanced settings would be similar to this (change NNNNN to your customfield IDs):
##Custom start-end interval
[jira.customfield_custom_resolution_interval]
name = "Custom start-end interval"
data_type = "integer"
dimension = true
javascript_code = '''
if(issue.fields.customfield_NNNNN && issue.fields.customfield_NNNNN) {
issue.fields.customfield_custom_resolution_interval =
(Date.parse(issue.fields.customfield_NNNNN) - Date.parse(issue.fields.customfield_NNNNN)) / 1000 / 60 / 60 / 24;
}
'''
time_unit = "days"
time_interval = "duration"
intervals = "11,51,101"
interval_unit = "days"
In the report it would look like this:
With this dimension, you will be able to create a summary about your issues split by different other dimensions.
For example, split by status:
Best,
Gerda // support@eazyBI.com
Hi Gerda,
Many Thanks for the reply. The solution does work for the time difference between created date and the custom start date field. However I have been calculating differences between specific transition status using the below time stamp formula:
TimestampToDate((
[Measures].[Transition to status last timestamp],
[Transition Status].[Awaiting Odds]
))
Unfortunately our workflows don’t store a date/time into a custom field for each transition due to the volume of different workflows/transitions. This means that the javascript provided above does not work for these ones as there is no custom field to look up.
Do you have any suggestions for an alternative way to summarise the results based on the values either being in/out of SLA?
Many Thanks
Carly
Hi @CarlyBooth,
Another option is to exploit DateDiffDays() function.
Here an example based on issue dates (but I hope you will be able to add there your status dates):
Let me know if this helps you to move forward or you need any more assistance!
best,
Gerda
Hello Gerda,
I have created the below calculated measure which almost works:
NonZero(
sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
Not IsEmpty([Measures].[Issues resolved])
AND
DateDiffDays(
TimestampToDate((
[Measures].[Transition to status last timestamp],
[Transition Status].[Awaiting Odds]
)),
[Issue].CurrentHierarchyMember.get(‘Start Date’)
) <=5 ),
[Measures].[Issues resolved]
)
)
The issue I have is that when I use the Time dimension to get the monthly summary, the calculated measure above is not picking up issues that have an awaiting odds time stamp in a different month the the month that the issues were resolved in.
I am trying to get to the point that I can summarise for say the month of November, how many of the 44 issues with a resolved date in november were within the 5 day SLA. When I complete it manually I know that there are 36 but 3 issues are being missed as the awaiting odds timestamp falls in October.
Many Thanks
Carly
Hi @gerda.grantina / @janis.plume
Are you able to share a suggestion please?
To provide more context I have provided examples below of how the Time Stamp is behaving with the Time dimension. When months are selected, issue GMPP-8334 does not have a timestamp shown as it falls into September.
When I remove Time from the report this issue now shows a timestamp but I am not able to summarise by month as required.
Please can you let me know if thereis anyway to amend the below formula so that I can see how many of the issues resolved in a specific month were were submitted outside of SLA even if the Submitted date was in the previous month.
Many Thanks
Carly
Hi @CarlyBooth ,
Mine apologies for the delayed answer!
Add [Time].Default member to your transition, so it ignores the Time filter applied, also use DateinPeriod() function to bind the report to the Time dimension by the resolution day.
It should look something like this (please adjust the formula to your measures where needed):
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember
)
AND
DateDiffDays(
[Measures].[Issue created date],
TimestampToDate(
([Measures].[Transition to status last timestamp],
[Transition Status].[In Progress],
[Time].DefaultMember)
)
) <= 10
),
[Measures].[Issues resolved]
)
In the report it is as this:
Let me know if this works as expected!
best,
Gerda
