In the example below, we're using the Due Date field. You can use any system or custom field.
| Code Block |
|---|
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
| Code Block |
|---|
SUM {
IF history.changes
.FILTER($.field = "sprint")
.LAST()
.changeGroup.timestamp > sprint.last().startDate: 1
} |
The example below shows who changed the Resolution field, but you can replace "resolution" with another system or custom field:
| Code Block |
|---|
history.changes
.FILTER($.field = “resolution”).last().changeGroup.author |
| Code Block |
|---|
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 |
| Code Block |
|---|
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() |
