How to sum custom field (history changes) values for each status for 3 months?

testovayulia · March 3, 2023, 9:01am

Hello,

Please, help me with the following report.
My task is to create a report that displays the number of days for each Issue that has a custom field “Blocking” + what statuses it was in.
We have created a single select custom field “Blocking” to track the history of changes.
This field can have a value of 0 or 1. (Ignore the value 4.00 - this was a test mode)
Time: last 3 months

At the current stage, the report looks like this:

I need to remove the “dates” from the report, and instead leave one field for each status.
This field should contain the total number of days.

So now we have this:

I would like to get this result:

How can I do it?

Thanks in advance

roberts.cacus · April 4, 2023, 10:21am

Hi @testovayulia,

Welcome to the eazyBI community. I am sorry your post didn’t get any attention for so long.

To get the number of days a custom field had a value in a particular status, I recommend a formula similar to the one below. The example considers the Jira system field “Flagged”, and calculates the number of days an issue was flagged as “Impediment”.

CASE WHEN
Not IsEmpty(([Measures].[Issues history],
[Transition Field].[Flagged],
[Flagged].[Impediment]))
THEN
DateDiffDays(
  TimeStampToDate(CoalesceEmpty(
    (
      [Measures].[Transition to last timestamp],
      [Transition Field].[Flagged],
      [Flagged].[Impediment]
    ),
    (
      [Measures].[Transition to status last timestamp]
    )
  )),
  TimeStampToDate(CoalesceEmpty(
    (
      [Measures].[Transition from last timestamp],
      [Transition Field].[Flagged],
      [Flagged].[Impediment]
    ),
    (
      [Measures].[Transition from status last timestamp]
    )
  ))
)
END

With the CASE statement, the formula checks whether the "Flagged " field had the desired value in each status. Then, it calculates the difference in days between the last date the value was changed to the desired one in the particular status and the last date value changed from it.
Suppose the field value didn’t change in a particular status. In that case, the field transition date will be empty, and the CoalesceEmpty() function replaces these values with the status transition dates.

See an example below:

The highlighted column is the calculated measure with the suggested formula. The rest are there for reference.

Please look at the eazyBI documentation page for more information on defining calculated measures - Calculated measures and members.

Best,
Roberts // support@eazybi.com