Calculate ageing based on custom field values

Hi @eazyBI-Staff,

We have a custom field(Stages) of type cascading which has Parent Value as “WT” and Level 1 values are,

1. First value/test
2. Second value/2nd test
3. Third value
4. Fourth value

We need a report to show number of days(Ageing) each issue has spent in each Level 1 value’s. It is easy when we try to look for a report with issue’s “Status”, but with the custom field value it’s bit tricky. I was able to get partial report with below custom measure(But the days spent in current value till now doesn’t come in the report).

Custom measure

DateDiffDays(TimestampToDate(([Measures].[Transition to first timestamp],
[Transition Field].[Stages])),TimestampToDate(([Measures].[Transition from last timestamp],
[Transition Field].[Stages])))

We’ve setup advanced setting for custom field to fetch issue’s history.

Cascading field's advanced setting
data_type = "string"
dimension = true
separate_table = true
changes = true
levels = ["Level 1","Level 2","Level 3"]
split_by = ","
javascript_code = '''
var cfList = [];
if (issue.fields.customfield_NNNNN) {
  for (var i=0; i < issue.fields.customfield_NNNNN.length; i++) {
    var cfVal= issue.fields.customfield_NNNN[i].value;
  issue.fields.customfield_NNNNN = _.uniq(cfList).join(",");

Appreciate your help on this. Thanks!

@ilze.leite Thanks for being so helpful with the solution! :handshake: :clap:

Custom measure condition that had solved above requirement,

--This function checks if issue's "Stages" field option matches the field's option list and returns "1" if it does 
 DefaultContext(([Measures].[Issues created],
--Calculates the difference in days b/w the current field option till date
  [Measures].[Transition to last timestamp],
  [Transition Field].[Stages]
--Calculates the difference in days from one field option to other option
  TimestampToDate(([Measures].[Transition to first timestamp],
  [Transition Field].[Stages])),
  TimestampToDate(([Measures].[Transition from last timestamp],
  [Transition Field].[Stages])))
1 Like