Calculate ageing based on custom field values

Questions & Answers
, ,
1

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 
[jira.customfield_17905]
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;
    cfList.push(cfVal)
  }
}
if(cfList){
  issue.fields.customfield_NNNNN = _.uniq(cfList).join(",");
}
'''

Appreciate your help on this. Thanks!

3

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

Solution:
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 
CASE WHEN
 DefaultContext(([Measures].[Issues created],
  [Issue].CurrentHierarchyMember,
  [Stages].CurrentHierarchyMember))>0
THEN
--Calculates the difference in days b/w the current field option till date
DateDiffDays(
TimestampToDate((
  [Measures].[Transition to last timestamp],
  [Transition Field].[Stages]
)),
Now()
)
ELSE
--Calculates the difference in days from one field option to other option
DateDiffDays(
  TimestampToDate(([Measures].[Transition to first timestamp],
  [Transition Field].[Stages])),
  TimestampToDate(([Measures].[Transition from last timestamp],
  [Transition Field].[Stages])))
END
1 Like