How To Calculate The Maximum / Minimum Date Per Categoty

Comments

20 comments

  • Avatar
    Evan York

    Any chance of the product supporting min/max on date fields without requiring this workaround?

    0
    Comment actions Permalink
  • Avatar
    Inbal

    Hi Evan,

    Thank you for your comment.

    Currently Sisense does not support min/max on date fields, but it's a feature planned for future version.

    Regards,

    Inbal

    0
    Comment actions Permalink
  • Avatar
    Pluripharm IT

    Hopefully it will be implemented soon.

     

    Till then this workaround needs to be used.

    Below you'll find a improvement to the script above so other localizations can be supported too.

    var thousand_separator = '.' //replace . with your local thousands separator
    var date_separator = '-' //replace - with your local date separator
    var dateColumns = [2,3,4]; //select the date columns that should be transformed
    var selections = "";
    for (var i = 0; i < dateColumns.length; i++) {
    selections += "td:nth-child(" + dateColumns[i] + ") div";
    if (i < dateColumns.length - 1) selections += ",";

    }
    widget.on('ready', function(se, ev){
    var e = element;
    var w = widget;
    var d = dashboard;
    _.each($(selections , e).not('.wrapper, .p-head-content'), function(cell){
    var num = parseFloat($(cell).text().split(thousand_separator).join(''));
    var Year = Math.floor(num/10000);
    var Month = Math.floor(Math.floor(num%10000)/100);
    var Day = Math.floor(num%100);
    if (!isNaN(Year)) {
    debugger;
    $(cell).text( Day + date_separator + Month + date_separator + Year );
    } else {
    $(cell).text('');
    }
    })
    })

    0
    Comment actions Permalink
  • Avatar
    Davi Chan

    Great, I got this working too thanks!

    Although as always prefer to see this function natively in SiSense.

    Cheers!

    0
    Comment actions Permalink
  • Avatar
    Sahar

    Hi, thanks... it's working :)

    would be nice if you could help with the possibility to see a day diff between max and min dates (e.g. day difference between first game date and last game date).

    Is there any supportive script?

    Off course, out of the box solution will be much appreciate.. any updates on that?

    Sahar

     

     

    0
    Comment actions Permalink
  • Avatar
    Dotan

    If you'd like to apply this method on an Indicator widget use the following code:

    widget.on('ready', function(se, ev){  
    setTimeout(function(){
    var num = $$get(ev, 'widget.rawQueryResult.values.0.data');
    var Year = Math.floor(num/10000);
    var Month = Math.floor(Math.floor(num%10000)/100);
    var Day = Math.floor(num%100);
    $($('.number_span', element)[0]).text( Day + "/" + Month + "/" + Year);
    }, 0);
    });
    0
    Comment actions Permalink
  • Avatar
    Sahar

    Thanks for the response

    If it applies for an indicator widget only then it might be helpful for other (very specific) things, but will not be relevant for my example.

    Nonetheless, If i do want to use it, where should i paste it, below/above/instead of the original script?

    Thanks,

     

    Sahar

    0
    Comment actions Permalink
  • Avatar
    Scott Vickers

    If anybody is trying to use this with an embedded indicator widget, code needs a slight adjustment:

    widget.on('ready', function(se, ev){  
    setTimeout(function(){
    var num = $$get(ev, 'widget.rawQueryResult.values.0.data');
    if(!num)
    num = $$get(ev, 'widget.queryResult.value.data');
    var Year = Math.floor(num/10000);
    var Month = Math.floor(Math.floor(num%10000)/100);
    var Day = Math.floor(num%100);
    $($('.number_span', element)[0]).text( Day + "/" + Month + "/" + Year);
    }, 0);
    });

     

    0
    Comment actions Permalink
  • Avatar
    Yoni Lerner

    Thanks to Omer - New script that works with a few categories.

    var m_names = new Array("Jan", "Feb", "Mar", 
    "Apr", "May", "Jun", "Jul", "Aug", "Sep",
    "Oct", "Nov", "Dec");

    var dateColumns = [4]; //select the date columns that should be transformed
    var num = 0;
    var selections = "";

    widget.on('ready', function(se, ev){
    $.each(dateColumns, function(index, value){
    num = $("tbody tr:first").children().length - value;
    var e = element;
    var w = widget;
    var d = dashboard;
    $("tbody tr" , e).not('.wrapper, .p-head-content').each(function(){
    var cell = $(this).children().last();
    for (var a = 0; a < num; a++){
    cell = cell.prev();
    }
    var num1 = parseFloat(cell.text().split(',').join(''));

    var Year = Math.floor(num1/10000);
    var Month = Math.floor(Math.floor(num1%10000)/100);
    var Day = Math.floor(num1%100);
    console.log(isNaN(Year));
    if (!isNaN(Year)) {
    var final_date = m_names[Month-1] +"-"+Day + "-" + Year;
    cell.text(final_date);
    } else {
    cell.text('');
    }
    })
    });
    });
    1
    Comment actions Permalink
  • Avatar
    Tripti Jaiswal

    Hi Alon,

     

    Follow all the steps as mentioned,but unexpected result.

    What can be the issue.

    Find the steps below:

    Custom Field query : 

    (10000*getyear([<FIELD NAME>])+100*getmonth([<FIELD NAME])+getday([<FIELD NAME]))

     

    Widget Script :


    var dateColumns = [2]; //select the date columns that should be transformed

    var selections = "";

    for (var i = 0; i < dateColumns.length; i++) {

    selections += "td:nth-child(" + dateColumns[i] + ") div";

    if (i < dateColumns.length - 1) selections += ",";

    }

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

    _.each($(selections , e).not('.wrapper, .p-head-content'), function(cell){
    var num = parseFloat($(cell).text().split(',').join(''));
    var Year = Math.floor(num/10000);
    var Month = Math.floor(Math.floor(num%10000)/100);
    var Day = Math.floor(num%100);
    if (!isNaN(Year)) {
    debugger;
    $(cell).text( Day + "/"+Month+"/"+Year);
    } else {
    $(cell).text('');
    }
    })
    })

     

    Result ->

    Regards

    Tripti GE

    0
    Comment actions Permalink
  • Avatar
    Yoni Lerner

    Hi Tripti,

     

    It's seems that your conversion of  the date field into numeric field didn't go as expected.

     

    you are supposed to receive a number that looks something like this for 12/30/2017 you will get the number 20171230

    0
    Comment actions Permalink
  • Avatar
    James Matthiesen

    Hi there, 

    Love this workaround. Very useful. 

    Is there any way to quickly use filter according to first date at the dashboard level? I've created the numeric date field, and the filter works great for specific pivot tables and widgets. However, if I have a dashboard with several pivot tables, it's a bit tedious to have to update the widget filter for every single widget in the dashboard.

     

    Thanks,

    Jimmy

    0
    Comment actions Permalink
  • Avatar
    Alina Lotman

    Hi,

    This workaround is great - however it seems that the indicator script is not working for 6.7 , any chance it can be updated? I am using the scripts from Dotan's comment.

    widget.on('ready', function(se, ev){  
    setTimeout(function(){
    var num = $$get(ev, 'widget.rawQueryResult.values.0.data');
    var Year = Math.floor(num/10000);
    var Month = Math.floor(Math.floor(num%10000)/100);
    var Day = Math.floor(num%100);
    $($('.number_span', element)[0]).text( Day + "/" + Month + "/" + Year);
    }, 0);
    });

    Thanks,

    Alina

    0
    Comment actions Permalink
  • Avatar
    Abdelrahman Mohamed

    Hi

    Is there any workaround for the indicator script as it is not working on the latest version 

    Thanks

    0
    Comment actions Permalink
  • Avatar
    Jeremy Naiden

    Is this native in Sisense yet?

     

    0
    Comment actions Permalink
  • Avatar
    Wojciech Obiedzinski

    Hi ,

     

    How is MIN / MAX on date filed not native to SIsense yet? we are in 2019.

    2
    Comment actions Permalink
  • Avatar
    Dot Q

    Does anybody have a script that can change the formatting of a BloX value? Screenshot attached.

    0
    Comment actions Permalink
  • Avatar
    Dot Q (Edited )

    So I tried this method but every script in this thread did not format the number correctly to a date. However, you can simply Select MIN([<date element name>]) in a custom SQL query and join that back to your original table. That worked for me.

    0
    Comment actions Permalink
  • Avatar
    JJ Chritton

    I also vote that this should be supported as native functionality in Sisense. In my use case, I want to take the max(date) for a category in a table. I don't think I'll make the effort to try to use all of the various scripted workarounds knowing that the next upgrade is likely going to break whatever workaround we put in place.

    2
    Comment actions Permalink
  • Avatar
    Michael Harmon

    I have this working for a Pivot, but unfortunately it is not working for a Table With Aggregation.  Any ideas?

    0
    Comment actions Permalink

Please sign in to leave a comment.