ServiceClarity provides built in date and time functions to support historic data collection through dynamically generated JQL. These functions are an extension to JQL that enable you to write JQL queries that ServiceClarity can use to extract your historic JIRA data. 

Perhaps the most important ServiceClarity date function is the simple DATE function. This function enables dynamic JQL queries to refer to the date of the data collection, a date that by default means “today” but could refer to any historical data, for which there is JIRA data available. 

The above screenshot shows the “Collect Live Metrics” dialogue and in this example ServiceClarity is being asked to collect JIRA data for all dates between 1st July 2017 and the 8th July 2017. We can make use of the DATE  function in JQL so that for each of these dates the function will rewrite the JQL query to use the correct date.

Take for example a simple count of the work in progress:

{base_jql} AND status WAS 'In Progress' ON '{DATE,yyyy-MM-dd}' 

When a ServiceClarity data collection is run using the above JQL, the DATE  function will calculate the correct date and substitute the formatted value into the JQL. 

ServiceClarity provides a complete set of useful date functions (which can be formatted if required using optional formatting rules):

  • {END_OF_DAY} 
  • {END_OF_LAST_MONTH} 
  • {END_OF_LAST_WEEK} 
  • {END_OF_LAST_YEAR} 
  • {END_OF_MONTH} 
  • {END_OF_NEXT_MONTH} 
  • {END_OF_NEXT_WEEK} 
  • {END_OF_NEXT_YEAR} 
  • {END_OF_WEEK} 
  • {END_OF_YEAR} 
  • {LAST_MONTH} 
  • {LAST_WEEK} 
  • {LAST_YEAR} 
  • {NEXT_MONTH} 
  • {NEXT_WEEK} 
  • {NEXT_YEAR} 
  • {DATE} 
  • {START_OF_DAY} 
  • {START_OF_LAST_MONTH} 
  • {START_OF_LAST_WEEK} 
  • {START_OF_LAST_YEAR} 
  • {START_OF_MONTH} 
  • {START_OF_NEXT_MONTH} 
  • {START_OF_NEXT_WEEK} 
  • {START_OF_NEXT_YEAR} 
  • {START_OF_WEEK} 
  • {START_OF_YEAR} 

To provide even greater control all of the above date functions can be extended with offsets - days, weeks months or years. For example,

  • {DATE(-1d)}  - the day before the date of collection
  • {START_OF_MONTH(-1d)} - the end of the month before the date of collection
  • {START_OF_MONTH(-6M)}  - 6 months before the date of collection start at the 1st
  • {START_OF_WEEK(-4w)}  - the Monday 4 weeks before the date of collection
  • {END_OF_WEEK(-1w)}  - the Sunday before the date of collection
  • {END_OF_MONTH(-1M)}  - the end of the month before the date of collection
  • {END_OF_MONTH(-3M)}  - the end of the month 3 months before the date of collection
  • {START_OF_YEAR(-1y)}  - the 1st January he year of the collection
Did this answer your question?