Skip to main content

Convert Seconds to Time Format in Pivot Tables

Comments

22 comments

  • Vibol

    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
  • Permanently deleted user

    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
  • Vibol

    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);

    })

    })

    1
  • Alvaro Alonso

    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
  • Pluripharm IT

    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
  • Permanently deleted user

    Amazing Holke!

    Thanks very much for this!

    0
  • Permanently deleted user

    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);
    })
    })
    
    0
  • SERVER

    Hi

    What I need change for use in column chart?

    thanks in advance.

    0
  • Malinda Jepsen

    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
  • SERVER

    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
  • Malinda Jepsen

    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
  • Malinda Jepsen

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

    0
  • Permanently deleted user

    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
  • Ilan Shichor

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

    0
  • Amministratore BI

    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
  • Arik

    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
  • Benjamin Spain

    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
  • Omer

    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
  • Kobbi Gal

    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
  • Delsaran Bigglesworth

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

    0
  • David Rogers

    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
  • Jean Nicolas Fine

    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

Please sign in to leave a comment.