Duration to time - format axis of graph

#1

I have a dataset having a duration, in seconds.

I can build a nice stacked chart out of it:
Screenshot%20from%202019-09-19%2015-20-14

Now those 8M seconds on the left axis. I would like to display them in HH:mm:SS, and I’m stuck.

I tried to work in the dashboard itself, by adding an advanced formula which produces a TimeSpan (return TimeSpan.FromSeconds( $duration$ );), but this does not show at all.
I added this formatted duration measure in the data point series, put it on its own axis, but without luck. The same happens if I return a string from the advanced formula.

I tried to do this in the cube itself. It’s easy, I see the duration in the data preview but it’s found as a dimension.

I can believe that this doesn’t work, because if Dundas is trying to add strings or dimensions something is bound to fail.

I did see Is Time Duration a Measure or Hierarchy - If measure how do I get make it a Measure but this does not help much in my case.

So the question is: How can I nicely format the display of the axis of my graph?

#3

Hi Guillaume,

One of the solutions would be to create a calculated element in the Data Cube and convert the numeric value of seconds to corresponding hh:mm:ss. Once you have that additional field in your Data Cube, you can use it in the metric set to display it on the left axis of the chart. Please note that, this is an additional field and you can always access the original numeric value of the seconds from the data cube. You can use a script like the following to convert seconds to hh:mm:ss in Calculated Element:

long hours = $Time$ / 3600;
long minutes = ($Time$ % 3600) / 60;
long seconds = $Time$ % 60;
String str = hours.ToString() + ":" + minutes.ToString() + ":" + seconds.ToString();
return str;

Here is the screenshot for the Data Preview in the Date Cube:
image

Once you have your Data Cube formatted, you can go to Metric Set and have the following arrangement:
image

Make sure to go to Visualization Tab of the Metric Set and add Time Formatted Field in the Left Axis Label under Axis Category as shown in the figure below:
image

I hope it helps in resolving your issue.

Regards,
Razi

#4

Hi @Inactive-Member-17814731, thanks for your help.

By doing this I can easily add a new element to the data cube indeed.

Unfortunately, when I follow your example to put time formatted as left axis, the axis becomes blank.

My guess is that it’s because of summation. I can hover all elements and indeed see in the tooltip the formatted time, but nothing appears in the axis.

In the same way, I tried to only update the total calculation formula with your example, hoping it would work. It didn’t.

I am not too sure where to go from here, to be honest.

#5

Hi Guillaume,

Make sure you have Bar Height set to Measure or the required Hierarchy. In my case, I have a measure, “Time” which is set as a Bar Height under Visualization tab of Data Analysis Panel. Please refer to screenshot below:

image

I hope it helps in resolving your issue.

Regards,
Razi