Convert Seconds to Time Format in Pivot Tables

Comments

21 comments

  • Avatar
    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.

    1
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    Ido Darnell

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

    })

    })

    0
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    Ido Darnell

    Amazing Holke!

    Thanks very much for this!

    0
    Comment actions Permalink
  • Avatar
    Ori Sandler

    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
    Comment actions Permalink
  • Avatar
    SERVER

    Hi

    What I need change for use in column chart?

    thanks in advance.

    0
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    Malinda Jepsen

    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
  • Avatar
    Ori Sandler

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

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

    0
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    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
    Comment actions Permalink
  • Avatar
    Delsaran Bigglesworth

    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

Please sign in to leave a comment.