Convert Seconds to Time Format in Pivot Tables

Aviad Harell

May 25, 2014 15:15

Introduction

With the code below you can create measures in the time format (M:SS) or (HH:MM:SS) and show them in a table.

For this example, you can find an excel file with Customer Name as a dimension and Time in the format M:SS as values.

Steps

Step 1

The first step is to import into the Elasticube the time field from the source as a column of type "date-time".

Step 2

Convert the Time field into seconds in a new custom field. To do so, create a new custom field of type Integer and add to it the following code:

GetHour([yourDateTimeField])*3600+GetMinute([yourDateTimeField])*60+GetSecond([yourDateTimeField])

The code above will calculate the number of seconds of a given time since the beginning of the day. Remember to input data in a 24hrs format.

Step 3

Build the ElastiCube to materialize the changes to the schema.

Step 4

In SiSense 5 web interface, create a new table widget and add to it the Customer name as Rows and the Minutes as Values.

Step 5

Click on the Options menu, Edit script, and add to the new window one of the following javascript codes:

To show the format in MM:SS - set the showHours parameter to false

in line 10, fill in the indices (order) of one or more columns that need to be transformed.

var thousand_separator = '.' //replace . with your local thousands separator
var time_separator = ':' //replace - with your local time separator
var showHours = true; 


widget.on('ready', function(se, ev){
var e = element;

 //input the column numbers that need to be converted by: ,p-value[fidx="X"] 0 being the first / leftmost dimension.	
_.each($('.p-value[fidx="4"]',e), function(cell){ 
	
	
var num = parseFloat($(cell).text().split(thousand_separator).join('')); 

var sign = num < 0 ? "-" : "";
num = Math.abs(num);
	
	if (showHours){

			var hours = (parseInt( num / 3600 ));
			var minutes = parseInt( (num - (hours * 3600)) / 60 );
			var seconds = num - (hours * 3600) - (minutes * 60) ;


			var hoursText = hours < 10 ? "0" + hours : hours;
	}
	else{

			var minutes = parseInt( num / 60 );
			var seconds = num % 60;
	}
		
	
var minutesText = minutes < 10 ? "0" + minutes : minutes;
var secondsText = seconds < 10 ? "0" + seconds : seconds;

	if (showHours)	
		$(cell).text( sign + hoursText + time_separator + minutesText + time_separator + secondsText);
	else
		$(cell).text(sign + minutesText + time_separator + secondsText);	
	
	
})
})

Step 6

Click on "Save" on the script tab, and later press "F5" to refresh the table. Click on "Save" to apply the changes to the widget.

time test.xlsx
time test.ecdata

Comments

22 comments

Vibol April 29, 2015 17:48

Great example, I was able to apply this walk through into my dashboards however it seems to only affect the first row of each group. Using the exact code above and setting the proper column, what else do I need to do to apply the conversion to all rows?

0

Comment actions Permalink
Ido Darnell May 04, 2015 15:14

One can alspo achieve hh:mm;ss by using the following script:

widget.on('ready', function(se, ev){
var e = element;
var w = widget;
var d = dashboard;
setTimeout(function(){

var cell = $("#secondary_span",e);
var num = parseFloat($(cell).text().replace(',',''));

debugger
var hours = parseInt( num / 3600 )%24;
var minutes = parseInt( num / 60 )%60;
var seconds = num % 60;

var result = (hours < 10 ? "0" + hours : hours) + ":" + (minutes < 10 ? "0" + minutes : minutes) + ":" + (seconds < 10 ? "0" + seconds : seconds);
$(cell).text(result);
},100)

})

0

Comment actions Permalink

Vibol June 04, 2015 17:56

If I wanted to add multiple columns to convert my values, how would I format that? Right now I’m converting the column "Avg Time on Page / Session”, but I also want to convert another column "Avg Time (Current Week - Past Week)”

------------

widget.on('ready', function(se, ev){

var e = element;

var w = widget;

var d = dashboard;

var data = w.metadata.panels[1].items

var returnedData = $.grep(data, function (element, index) {

return $(element.jaql).attr("title") == "Avg Time on Page / Session";

});

_.each($('td[fidx='+returnedData[0].field.index+'] div' , e).not('.wrapper, .p-head-content'), function(cell){

var num = parseFloat(($(cell).text()).replace(/\,/g,''));

var hours = parseInt( num / 3600 )%24;

var minutes = parseInt( num / 60 )%60;

var seconds = num % 60;

var hoursText = hours < 10 ? "0" + hours : hours;

var minutesText = minutes < 10 ? "0" + minutes : minutes;

var secondsText = seconds < 10 ? "0" + seconds : seconds;

$(cell).text( hoursText + ":" + minutesText + ":" + secondsText);

})

Alvaro Alonso June 11, 2015 12:03

can the Javascript above provided, be implemented on other widgets apart from the table widget?

e.x: can I make a line chart were it shows in the vertical axis the # of seconds in hh:mm:ss ?

0

Comment actions Permalink
Pluripharm IT August 10, 2015 08:45

The script below solves multiple columns problem.

See also: https://support.sisense.com/entries/59761584-How-To-Calculate-The-Maximum-Minimum-Date-Per-Categoty

var dateColumns = [2,3]; //select the time columns that should be transformed
var thousand_separator = '.' //replace . with your local thousands separator
var time_separator = ':' //replace - with your local time separator

var returnedData = "";
for (var i = 0; i < dateColumns.length; i++) {
returnedData += "td:nth-child(" + dateColumns[i] + ") div";
if (i < dateColumns.length - 1) returnedData += ",";

}

widget.on('ready', function(se, ev){
var e = element;
var w = widget;
var d = dashboard;

_.each($(returnedData , e).not('.wrapper, .p-head-content'), function(cell){
var num = parseFloat($(cell).text().split(thousand_separator).join(''));

var sign = num < 0 ? "-" : "";
num = Math.abs(num); //
console.log('num')
console.log(num)

var hours = (parseInt( num / 3600 ));
console.log('hours')
console.log(hours)

var minutes = parseInt( (num - (hours * 3600)) / 60 );
console.log('minutes')
console.log(minutes)

var seconds = num - (hours * 3600) - (minutes * 60) ;
console.log('seconds')
console.log(seconds)

var hoursText = hours < 10 ? "0" + hours : hours;
var minutesText = minutes < 10 ? "0" + minutes : minutes;
var secondsText = seconds < 10 ? "0" + seconds : seconds;

$(cell).text( sign + hoursText + time_separator + minutesText + time_separator + secondsText);
})
})

0

Comment actions Permalink
Ido Darnell August 10, 2015 09:11

Amazing Holke!

Thanks very much for this!

0

Comment actions Permalink

Ori Sandler November 30, 2015 08:52

The following script can also be used on a Pie chart:

widget.on('ready', function(se, ev){

var titles = $(element).find('.highcharts-data-labels').find('tspan:nth-child(2n)');

_.each(titles, function(title){ 

var num = $(title).text();
num = num.replace(/,/g, "");
if (num.indexOf('K') > 0 || num.indexOf('k') || num.indexOf('M') > 0 || num.indexOf('m') || num.indexOf('B') > 0 || num.indexOf('b') || num.indexOf('T') > 0 || num.indexOf('t') > 0){
num = (num).substr(0 , num.length - 1); 
num = num*1000;
}

var sign = num < 0 ? "-" : "";

var hours = (parseInt( num / 3600 )%24);
var minutes = parseInt( num / 60 )%6
var seconds = num % 60;

var hoursText = hours < 10 ? "0" + hours : hours;
var minutesText = minutes < 10 ? "0" + minutes : minutes;
var secondsText = seconds < 10 ? "0" + seconds : seconds;

$(title).text( sign + hoursText + ":" + minutesText + ":" + secondsText);
})
})

SERVER February 11, 2016 20:16

Hi

What I need change for use in column chart?

thanks in advance.

0

Comment actions Permalink
Malinda Jepsen February 16, 2016 17:18

I'm trying to use this in an Indicator widget and can't seem to get the right syntax to access the indicator's value.

0

Comment actions Permalink
SERVER February 16, 2016 17:40

Hi

Malinda try with this code

//------------------------------------------------------------

widget.on('processresult',function(widget,result) {

//Get the Widget ID
var wid = widget.oid;

//Get the value
//var num = parseFloat(span.text().replace(/\,/g,''));
var num = result.result.value.data;

//Function to convert the time
var convertTime = function(num,wid) {

//Only run if the value is numeric
if ($.isNumeric(num)) {

//Parse out hours/min/sec

var dias = Math.round(parseInt( num / 86400 ));

var hours = parseInt( num / 3600 )%24;
var minutes = parseInt( num / 60 )%60;
var seconds = Math.round(num % 60);

//Figure out the text to display instead
var diasText = dias < 10 ? "0" + dias:dias;

var hoursText = hours < 10 ? "0" + hours : hours;
var minutesText = minutes < 10 ? "0" + minutes : minutes;
var secondsText = seconds < 10 ? "0" + seconds : seconds;

var newText = diasText + " Dias "+ hoursText + ":" + minutesText + ":" + secondsText;

//Find the widget span
var widget = $('widget[widgetid='+wid+']');
var span = $('#number_span',widget);

//Write back the text
span.text(newText);
}
};

//Write back the formatted text
setTimeout(function(){
convertTime(num,wid);
},1000);
})

//----------------------------------------------------------------------

0

Comment actions Permalink
Malinda Jepsen February 16, 2016 19:24

Thank you "SERVER". Unfortunately, it didn't work "plug and play", so now I'm trying to debug it to figure out what isn't working as I expect.

0

Comment actions Permalink
Malinda Jepsen February 16, 2016 20:28

Edit my comment: It was not working in the previewer of the widget, but does work on the dashboard. Thanks again!

0

Comment actions Permalink
Ori Sandler February 19, 2016 07:19

Hey!

Adding also a script for an indicator, hope this helps!

widget.on('ready', function(se, ev){
var titles = $(element).find('span.number_span');
_.each(titles, function(title){
var num = widget.queryResult.value.data.toString();

if (num.indexOf('K') > 0 || num.indexOf('k') > 0){
num = (num).substr(0 , num.length - 1);
num = num*1000;
}

num = parseFloat(num.replace(/\,/g, ''));

var sign = num < 0 ? "-" : "";
var hours = parseInt(num / 3600 )%24;
var minutes = parseInt( num / 60 )%60;
var seconds = num % 60;
var hoursText = hours < 10 ? "0" + hours : hours;
var minutesText = minutes < 10 ? "0" + minutes : minutes;
var secondsText = seconds < 10 ? "0" + seconds : seconds;
setTimeout(function(){
$(title).text( sign + hoursText + ":" + minutesText + ":" + secondsText);
}, 10);
})
})

0

Comment actions Permalink
Ilan Shichor June 27, 2016 15:47

Here's how to do it in Bar/Column/Line Charts: https://support.sisense.com/entries/100177527

0

Comment actions Permalink
Amministratore BI August 08, 2016 13:05

Hi, I tried the different scripts, but I have problems with all of them:

The first one gives me nothing.. but really nothing, I mean, no change at all..

So I tried with Holke's script, that is working only if I have a pivot with all the columns with some values in it, if I have a pivot that groups elements, my problem is that then it seems to calculate the columns with other numbers, so I should put in different columns, but then if I got some numbers that I don't want formatted, I'm still in troubles

For example I'll try to replicate one of my tables:

Name || calls || date and hour of the calls || duration of the call

Giovanni|| inbound || 01/08/2016 - 08:00:00 || 00:01:30

|| || 01/08/2016 - 08:25:00|| 134

So as you can see the second row doesn't receive the correct formatting, as the dashboard seems to think that it's the 2 column and not the 4th column..

Any suggestions?

Because I also tried to save directly the values on the elasticube with a sql (https://support.sisense.com/entries/50745924-Modulo-and-formatting-seconds-as-HH-MM-SS) , but then I have problems in doing sums and so on..

0

Comment actions Permalink
Arik September 05, 2016 11:03

Hi Giovanni ,

The script in the body of the post was updated (and consolidated) to be compatible with the latest Sisense release.

Please update your code in the comments if needed.

Hope this will work better now.

Best,

Arik

0

Comment actions Permalink
Benjamin Spain December 16, 2016 18:55
I'm not seeing 'element' as defined in the widget.on('*') callbacks. Is that something that changed in later versions of Sisense?

widget.on('ready', function(se, ev){
var e = element; // <-- undefined

I am able to use the ev.widget.oid to find the element instead
```
widget.on('ready', function(se, ev)
{
    var table = $('#' + ev.widget.oid)
```
0

Comment actions Permalink
Omer January 04, 2017 10:01

Hi Benjamin,

The element is supposed to be defined.

Make sure you call it from within an instance, for example:

dashboard.on('widgetready', function(sender, ev){

var e = element;

}

Regards,

Omer

0

Comment actions Permalink
Kobbi Gal September 05, 2017 15:34
Updated script.

Found a calculation issue in minutes when:
```
showHours = true
```
changed from:
```
var minutes = parseInt(num/60)%60;
```
To:
```
var minutes = parseInt(num/60);
```
0

Comment actions Permalink
Delsaran Bigglesworth August 20, 2018 17:52

Does this still work in 7.1.3? I'm trying to use it on a pivot chart to no avail.

0

Comment actions Permalink
David Rogers April 19, 2019 17:58

This is also an inadequate solution in that the field will alpha sort rather than sort linearly. Sisense just needs to enrich their formatting capabilities rather than constantly using javascript workarounds.

2

Comment actions Permalink
Jean Nicolas Fine March 17, 2020 15:11

Hi, I tried all this script on an aggregated table but none of them works : I just want to convert a column from integer (seconds) to HH:MM:SS.

Do you have any advice ?

Thank you for your help.

1

Comment actions Permalink

Please sign in to leave a comment.