Jira Assets: Object Status - Custom "Hours in transition status" measure which includes the time in the current status

Questions & Answers
, ,
1

Hi all,

I’m currently trying to create a custom measure which enhances the predefined “Hours in transition status” measure.

Problem with the predefined measure: it only takes the time between completed status changes into account. It ignores the time an object is spending in the current status from the time of the last change till now.

Solution proposal:

  • Create a new custom measure “Total hours in transition status”
  • Identify the latest “Transition to status last date” date for each object
  • Use a CASE or IIF Statement to distinguish the calculation method of “Hours in transition status”
  • If “Transition to status last date” is NOT the latest date for the object use the predefined “Transition to status last date” measure for determining the “Total hours in transition status”
  • If “Transition to status last date” IS the latest date for the object calculate the difference between NOW and the “Transition to status last date” timestamp as “Total hours in transition status”

Does this solution make sense, or do I have a logical mistake in there? I’m struggling in building the corresponding MDX. In particular I’m failing currently with the step “Identify the latest “Transition to status last date” date for each object”. Once I have this in place, I’m hopefully able to build the rest of the formula.

Can somebody tell me how to achieve that?

Thanks in advance,
Patrick

2

Hello @Patrick83,

Thank you for posting your question!

The general direction of the proposed solution is correct, just a few notes:

  • You need to use the DefaultMember for the Time dimension as it would then return the result for your query regardless of when it has happened.

  • Same needs to be done for the Object Transition status dimension to return results regardless of the Object Transition Status

I would suggest using the following CASE statement to get results for Object Days in Current status:

CASE WHEN
[Measures].[Object status]=[Object Transition Status].CurrentMember.Name
THEN
  DateDiffDays(([Measures].[Transition to status last date],
  [Object Transition Status].DefaultMember,
  [Time].DefaultMember),
Now())
END

Please note that Formatting needs to be Numeric → Decimal as we need to get a numeric value.

In my example below, you can see that Object “Anna Monitor” has been in the current status “Active” for 8.13 days, while Object “Daina Monitor” has been in the status “In Service” for 8.13 days.

image.png
image.png1806×1270 167 KB

I hope this helps!

Best,
Marita form support@eazybi.com

3

Hi @Marita_Norvele,

Thanks for your reply. Unfortunately, your proposed solution is not working.

a) The proposed CASE statement doesn’t work on its own. “[Object Transition Status].CurrentMember.Name” returns me always “All Transitions Statuses” in all cells. I believe that’s why it can’t find any matching case.

b) I’m looking for something slightly different. I have created this mockup table to visualize how the table should look like (the columns Hours in transition status and Hours in current status are obsolete at the end). Forget the numbers, they are only examples.

image
image934×611 12.2 KB

The only challenge I face currently is to get the “Hours in current status” calculated only after the last transition. My idea is to use MAX() on the [Transition to status last date] and only calculate the DateDiffHours between [Transition to status last date] and Now() only for the latest [Transition to status last date] of an object.

Any idea how to achieve this?

Kind regards,
Patrick

4

Hello @Patrick83,

Just to double check: are you using dimension Object Transition Status? Because if you are using Object Status dimension, you could get the results you are describing.

Thanks to double check and revert.

As for the point b), are you using the Default hierarchy or a Weekly one? For a weekly hierarchy, the following function should work:

CASE WHEN
[Measures].[Object status]=[Object Transition Status].CurrentMember.Name
THEN
  DateDiffDays(([Measures].[Transition to status last date],
  [Object Transition Status].DefaultMember,
  [Time].CurrentHierarchy.DefaultMember),
Now())
END

If you need more help, feel free to reach out to support@eazybi.com

Thanks,

Marita // support@eazybi.com

5

Hi @Marita_Norvele,

Thanks for coming back on this topic.

I have double checked it:

  • [Object Transition Status].CurrentMember.Name returns “All Transition Statuses”.
  • [Object Status].CurrentMember.Name returns “All Statuses”
  • [Measures].[Object status] returns the corresponding status (as intended)

I’m using the default time hierarchy set to Weekly → Week.

What I’m still missing in your proposed solution is the identification of the row with the latest [Transition to status last date] per object. This would be in my opinion the function I would need for the “CASE WHEN” clause instead of the currently proposed one. Comparing the [Object status] with the [Object Transition Status] will not help to identify that if I’m not miss leaded.

Thanks and regards,
Patrick

6

Hello @Patrick83,

The function I provided is returning results for the last status the object is in.

To illustrate, please see the example below: Object CA-72 (Anna Monitor) has changed the status a couple of times. There were some changes on the 14th of February and then again on the 5th of March 2024.

image.png

When I create a report using the CASE WHEN function I provided in this conversation before, I get the days since the object entered the last (current) status:

image.png

In order for us to help you better with your use case, please send your report definition to support@eazybi.com

(here’s how to do it: Export and import report definitions)

Thanks,

Marita // support@eazybi.com

7

Hi @Marita_Norvele ,

Thanks for your feedback!

I will have a look at it.

Regards,
Patrick