by Ian Macdonald, Principal Technologist, Pyramid Analytics

In many analytic applications, we may want to analyse and display the duration of activities. For example, the amount of time a support ticket is open, or the amount of time a consultant has spent on a project.

This time duration is often recorded in minutes, but the display of the duration requires formatting in terms of days, hours and minutes (and seconds potentially).

BI Office supports hours and minutes formatting using the 'hh:mm' or 'hh:mm:ss' format string in the Custom Format option in the Calculation Designer or Custom Member MDX editor. It works on "decimal days", so take your number of minutes and divide by 1440 (the number of minutes in a day) to get the decimal days. This will automatically display decimal days as hours and minutes without having to do complicated coding to derive the number of hours, minutes etc.

However, the hh:mm:ss format does not extend to days, it only displays as hours and minutes the decimal part of decimal days (i.e. if decimal days is 1.25, it will show 06:00:00).

But, good news, formats can be dynamic in MDX and changed based on MDX expressions. Thus, if the number of minutes exceeds 1440 (the number of minutes in a day), then you can calculate how many whole days are present and add that to the formatted hours and minutes string.

IIF( ( [Measures].[Quantity]  /1440) < 1, '"0 days" hh:mm' , '"' + cstr(int( [Measures].[Quantity]  /1440)) + ' days" hh:mm')

The MDX expression should be entered using the Custom Formulation option from the Formats drop down in the Calculation Designer or Custom Member MDX editor. Here, we’re using [Measures].[Quantity] as the number of minutes recorded:

When the custom measure is shown in the grid, the duration is displayed in “n days, 00:00” time format:

By default, the Y Axis on the chart will show the Measure Format, which will be the long format string created by the custom formulation expression. To show the number of days on the chart, use "Raw Format" option on the Y Axis, more options dialogue.