Calculating Days in Custom Field

Questions & Answers
, ,
1

Would greatly appreciate your help.

I’m trying to calculate how long something has been in a custom field.
I have a custom field labeled as “sub-status” and would like to know if there is an ability to calculate the difference when user selected one of the sub-statuses and today’s date.

I tried → DateDiffDays([Measures].[Sub-Status],Now())
without luck.

Thank you,

2

Hi @Kiki_M,

You can analyze the change history of single-select custom fields.

  1. First, make sure to import the change history of the custom field “Sub-Status”.
    In eazyBI advanced settings, add additional settings to enable the “import value changes” for the custom field.
    Please see the documentation and an additional setting example here: Import issue change history - custom fields.
    When settings are in place, go to import options and select import value changes.

  1. Now, you can use the dimension “Sub-Status” with measures designed for issue change history analysis (see the measure list here Import issue change history - Measures ).
    For example, to get the most recent date when the custom field was changed, you might use a tuple of hidden measure “Transition to last timestamp”, hidden dimension “Transition Field” and custom field dimension of interest.

    TimestampToDate(
  ([Measures].[Transition to last timestamp],
  [Transition field].[Sub-Status])
)

    You may also specify a particular Sub-Status, by adding [Sub-Status].[specific staus value] to the tuple expression.

  2. To calculate the duration between "sub-status change and today, you may use the function DateDiffDays() like this:

    DateDiffDays(
   TimestampToDate(
     ([Measures].[Transition to last timestamp],
     [Transition field].[Sub-Status])
  ),
  'today'
)

    Documentation with more details on calculated measures is here: Calculated measures.

Here is a similar use case on how to get the date of custom field changes:

Best,
Zane / support@eazyBI.com