how to put a specific format for a measure

[carolina (Carolina Balderrama)]

Hello,

I have a field in my cube called hours, I need to calculate the AVG of hours in the dashboard. But I need it to be shown in the format HH:MM:SS, I've tried Having the hours in the cube in that format (but the field becomes a string) so in the dashboard is shown as a hierarchy and don't allow me to do AVG.

I also tried to put just the hours as a double, then in the dahsboard I can calculate the average , but I need to show it in that specific format, any idea how can I do that?

Thank you..

[mohammad.qasim]

Hi Carolina,

Do you have any filters on the chart in the dashboard or just you have hours measure and your hierarchy in the rows and you want to do the avg of hours.

could you please share excel file for sample of your data and settings of data analysis panel of the chart.

I might get you solution for your case.

[ariel.pohoryles.1 (Ariel Pohoryles)]

Since a calculation is required to get the HH:MM:SS result and that result is a string and not a number, the easiest way to do so may be at the data cube level. That means that your data cube will need to return the avg hours results (vs getting the avg at the metric set level). One way to get the avg at the data cube level, you can use the aggregate transform.

Then, once you have an avg field in your data cube, you can add a calculated element transform that will return a string with the desired result using the following script (note the $HoursAvg$ placeholder needs to be changed to match your hours avg field name):

TimeSpan timespan = TimeSpan.FromHours($HoursAvg$);
string output = timespan.ToString("hh\\:mm\\:ss");
return output;

Here are some screen shots showing what it would like:

[carolina (Carolina Balderrama)]

Hi,

Yes I have two filters (date, and group) as slicers in my panel, and conected to the proper filters. And its actually not a chart, just the value (in a table or data label)

This is how my data analysis panel looks.(with the avg for the hours field as a double) I would like exactly this but shown in the correct format.(HH:MM:SS)

And this is an extract of the data:

So as you can see, im actually calculating the resolution_hours(double) or resolution_time_hours(string) from the create_at_ticket_date - resolved_at_ticket_date.

And I need to get the avg of the resolution_hours or the resolution_time_hours but It depends on the filters selected, for example between this date and that date, or only for Citi group. That's why I dont see a way to do it in the cube.

Let me know if you need to know anything else and thank you very much for your help.

[carolina (Carolina Balderrama)]

Hi,

Thank you for your help, but I dont think this solutions works for me, because I have some filters that I need to use. I explained my case a little more in the below comment, and I also put an example of the data that I have in my cube.

Thanks..

[ariel.pohoryles.1 (Ariel Pohoryles)]

Note that even if you have filters, you can still connect those to the data cube but this may require using bridge parameters that is indeed a more advanced topic.

Here is a solution that is entirely at the metric set level so it won't require changing anything in your data cube. The idea would be to create 3 additional measures in your metric set using formulas. One measure for the hours, another for the minutes and the last one for the seconds. You can use the following formulas for each measure (in your case the placeholder in the formula should be something like $resolution_hours$ instead of my $AvgHours$):

Hours:

Math.Floor(Math.Abs($AvgHours$))

Minutes:

Math.Floor((Math.Abs($AvgHours$) % 1) * 60)

Seconds:

(((Math.Abs($AvgHours$) % 1) * 60) % 1) * 60

Once you create those measures, you can format the hours to N2 and the minutes and seconds to 00 like so:

Then all you need to do in your data label is to set the label text property to use the hours, minutes and seconds placeholders with a colon in between them like so:

[carolina (Carolina Balderrama)]

Ok I think this would totally work.

Thank you very very much!

[mohammad.qasim]

Hi Carolina,

since you want to show the hours in a datalabel which make your mission is easy to be done using JS.

To do it, drag a label to the dashboard. Then on the dataLabel you have that shows the average hours ---> create a script under "Data Changed" interaction and paste the following script:

//********************************

var totalSeconds = dataLabel1._metricSetBindings[0].dataResult.cellset.cells[0][0].value*60*60;
if (totalSeconds == 0)
{
label1.labelText=hours+":"+minutes+":"+seconds;
}
else
{
hours = Math.floor(totalSeconds / 3600).toString();
totalSeconds %= 3600;
minutes = Math.floor(totalSeconds / 60).toString();
seconds = (totalSeconds % 60).toString();

if (hours.length == 1)
hours = '0'+hours;
if (minutes.length == 1)
minutes = '0'+minutes;
if (seconds.length == 1)
seconds = '0'+seconds;

label1.labelText=hours+":"+minutes+":"+seconds;
}

//*************************

Supposing your datalabel is named as dataLabel1 and the label is named as label1.

But I am surprised in your example why the average hours is shown in minus. I beleive it must have positive value.

Then you can drag the datalabel outside the canvas or making its opacity to be 0. in other words, no need to make it visible on the dashboard.

[mohammad.qasim]