Sample Formulas - Historical Values

Show the historical value of an issue field at a specific date

In the example below, we're using the Due Date field. You can use any system or custom field.

historical_value(this, "duedate", datetime("15/May/18 6:24 PM"))

Note: this formula also uses the Datetime function

Show the number of tasks added since the last sprint began

SUM { 
 IF history.changes
	.FILTER($.field = "sprint")
	.LAST()
	.changeGroup.timestamp > sprint.last().startDate: 1 
}

Show who changed the field value

The example below shows who changed the Resolution field, but you can replace "resolution" with another system or custom field:

history.changes
    .FILTER($.field = “resolution”).last().changeGroup.author

Time Flagged: Time the task was marked with a flag

with flag_change_time(value) =
history.changes
  .filter($.field = "flagged")
  .filter($.to = value)
  .changeGroup.time :
with flag_on_time = flag_change_time("Impediment") :
with flag_off_time = flag_change_time("") :IF flag_on_time && flag_off_time : flag_off_time - flag_on_time
ELSE IF flag_on_time : now() - flag_on_time

Time in status for a specific month

WITH year = 2023:
WITH month = 1: // 1 for Jan, 12 for Dec 
WITH keyStatus = "in progress": // key-insensitive
WITH calendar = "Standard work calendar 8/5": // other option is Standard calendar 24/7, the value is locale-dependant, also Gantt calendars are available
WITH startDate = MAKE_DATE(year, month, 1):
WITH finishDate = MIN(DATE_ADD(startDate, 1, "month"), NOW()):
WITH isStart(change) = change.from != keyStatus AND change.to = keyStatus:
WITH isFinish(change) = change.from = keyStatus AND change.to != keyStatus:
WITH intervalFits(start, finish) 
  =  start >= startDate AND start <= finishDate
  OR finish >= startDate AND finish <= finishDate
  OR start < startDate AND finish > finishDate:

WITH statusChanges = history.changes
  .FILTER($.field = "status" AND ($.isStart() OR $.isFinish())):
WITH times = MERGE_ARRAYS(
  IF statusChanges.FIRST().isFinish(): MIN(startDate, statusChanges.FIRST().changeGroup.time),
  statusChanges.changeGroup.time,
  IF statusChanges.LAST().isStart(): MAX(finishDate, statusChanges.LAST().changeGroup.time)
):

IF times: SEQUENCE(0, times.SIZE() - 1)
  .FILTER(MOD($, 2) == 0 AND intervalFits(times.GET($), times.GET($ + 1)))
  .MAP(CALENDAR_DURATION(MAX(times.GET($), startDate), MIN(times.GET($ + 1), finishDate), calendar))
  .SUM()