JQL convert to calculated measure

Questions & Answers
,
1

I want to convert below jql to calculated measure in project level.

“SLA date” > startofMonth() and status != Done

“SLA date” < startofMonth() and “Permit Date” > startofMonth and status != Done

Please note that “SLA date” and “Permit Date” are customfield in jira

Could I receive any help?

2

Hi @jin_woo_EUM,

Welcome to the eazyBI community :raised_hands:

Please keep in mind that eazyBI assigns Jira Service Management issue metrics to the Time dimension only for issues with completed cycles. Issues with ongoing cycles are not tied to the Time dimension, and the suggested calculations won’t work.

For the first calculation, you can try to define a calculated measure that considers the SLA issues that completed their cycle in the current month and are currently in a status that is not Done. The calculated measure formula could look similar to the one below:

NonZero(([Measures].[Time to first response Completed cycles issues count],
[Time].[Month].CurrentDateMember,
[Status].CurrentHierarchy.DefaultMember)
-
([Measures].[Time to first response Completed cycles issues count],
[Time].[Month].CurrentDateMember,
[Status].[Done]))

Replace the “Time to first response Completed cycles issues count” measure with the SLA measure you want to consider.

For the second request, comparing two date fields complicates the calculation. eazyBI has to consider each individual issue for that. The calculated measure formula could look similar to the one below:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateAfterPeriodEnd(
      [Measures].[Issue permit date],
      [Time].[Month].CurrentDateMember.PrevMember
    )
  ),
  ([Measures].[Time to first response Completed cycles issues count],
  [Time].CurrentHierarchy.DefaultMember,
  [Status].CurrentHierarchy.DefaultMember)
  -
  ([Measures].[Time to first response Completed cycles issues count],
  [Time].[Month].CurrentDateMember,
  [Status].[Done])
)

This calculated measure iterates through all issues and that can increase the report load time. Please see the eazyBI documentation page for more details on defining calculated measures - Calculated measures and members. I also recommend watching my colleagues presentation on “translating” JQLs into eazyBI reports and calculations - Training videos on specific topics.

Best,
Roberts // support@eazybi.com