Convert Seconds to Time Format in Pivot Tables
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.
-
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)})
-
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);
})
})
-
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 separatorvar 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);
})
}) -
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); }) })
-
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);
})//----------------------------------------------------------------------
-
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);
})
}) -
Here's how to do it in Bar/Column/Line Charts: https://support.sisense.com/entries/100177527
-
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..
-
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
-
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; // <-- undefinedI am able to use the ev.widget.oid to find the element instead
widget.on('ready', function(se, ev)
{
var table = $('#' + ev.widget.oid)
Please sign in to leave a comment.
Comments
22 comments