Sample Formulas - History

Historical value of a field

Shows the historical value of a field at a specified date and time.

CODE

WITH  HISTORICAL_VALUE(field, fieldHistory, date) = 
       IF fieldHistory.IS_EMPTY() AND field : field 
       ELSE fieldHistory.FILTER($.time <= date).toText :
HISTORICAL_VALUE(duedate, duedateHistory, datetime("15/May/18 6:24 PM"))

To customize this formula:

Replace the duedate and duedateHistory with another field
Replace the date and time

Time in status for a specific month

Calculates the time each issue spent in a specified status during a specified month. To customize, change the year, month, and keyStatus values.

CODE

WITH year = 2024:
WITH month = 6: // 1 for Jan, 12 for Dec 
WITH keyStatus = "In Progress": // key-insensitive
WITH startDate = MAKE_DATE(year, month, 1):
WITH finishDate = MIN(DATE_ADD(startDate, 1, "month"), NOW()):
WITH isStart(change) = change.fromText != keyStatus AND change.toText = keyStatus:
WITH isFinish(change) = change.fromText = keyStatus AND change.toText != keyStatus:
WITH intervalFits(start, finish) 
  =  start >= startDate AND start <= finishDate
  OR finish >= startDate AND finish <= finishDate
  OR start < startDate AND finish > finishDate:

WITH statusChanges = statusHistory
  .FILTER($.isStart() OR $.isFinish()):
WITH times = MERGE_ARRAYS(
  IF statusChanges.FIRST().isFinish(): MIN(startDate, statusChanges.FIRST().time),
  statusChanges.time,
  IF statusChanges.LAST().isStart(): MAX(finishDate, statusChanges.LAST().time)
):
IF times: SEQUENCE(0, times.SIZE() - 1)
  .FILTER(MOD($, 2) == 0 AND intervalFits(times.GET($), times.GET($ + 1)))
  .MAP(HOURS_BETWEEN(MAX(times.GET($), startDate), MIN(times.GET($ + 1), finishDate)) * 1000)
  .SUM()