Skip to main content

Filter Dates to Dynamically Start Today Through End of Month

Comments

7 comments

  • Elliott Herz

    Here is a script that I created that is pretty close. Based on a filter date, the dashboard script looks up the max date in the filter that is not greater than today and defaults to that date. The only addition, would be to create a date range instead of defaulting to a single value. The script also prints out all the filter dimensions to the browser console to be customized on, so I suggest checking out the console once you apply the script.

     

    var defaultFilterConfig = {

        dim1: "[DIM_DATE.DATE (Calendar)]"

    };

     

    dashboard.on('initialized', function(d, args) {

                    //print out all dimensions

                    for(var i=0; i<args.dashboard.filters.$$items.length; i++) {

                                    if(args.dashboard.filters.$$items[i].levels != null) { //this is for dependent filters

                                                    for(var j=0; j<args.dashboard.filters.$$items[i].levels.length; j++) {

                                                                    console.log(args.dashboard.filters.$$items[i].levels[j].dim);

                                                    }

                                    }

                                    else { //normal filters

                                                    console.log(args.dashboard.filters.$$items[i].jaql.dim);

                                    }

                    }

                   

                    //query the filter dimension to retrieve all members sorted descending

                    var dimJaql = '{"dim": "' + defaultFilterConfig.dim1 + '", "sort": "desc"}';

                    var query = '{"datasource": ' + JSON.stringify(args.dashboard.datasource) + ', "metadata": [' + dimJaql + ']}';

                    var url = '/api/elasticubes/' + args.dashboard.datasource.title + '/jaql';

                    $.ajax({

                                    async:false,

                                    method: 'POST',

                                    url: url,

                                    data: query,

                                    success: function(result) {

                                                    //find the date that is most up to date based on today

                                                    for(var x=0; x<result.values.length; x++) {

                                                                    if(result.values[x][0].data.substring(0,10) <= (new Date()).toISOString().substring(0,10)) {

                                                                                    break;

                                                                    }

                                                    }

                                                   

                                                    for(var i=0; i<args.dashboard.filters.$$items.length; i++) {

                                                                    if(args.dashboard.filters.$$items[i].levels != null) { //this is for dependent filters

                                                                                    for(var j=0; j<args.dashboard.filters.$$items[i].levels.length; j++) {

                                                                                                    if(defaultFilterConfig.dim1 === args.dashboard.filters.$$items[i].levels[j].dim) {

                                                                                                                    args.dashboard.filters.$$items[i].levels[j].filter = {

                                                                                                                                    explicit: true,

                                                                                                                                    members:[result.values[x][0].data],

                                                                                                                                    multiSelection: false,

                                                                                                                                    userMultiSelect: false

                                                                                                                    };

                                                                                                    }

                                                                                    }

                                                                    }

                                                                    else if(defaultFilterConfig.dim1 === args.dashboard.filters.$$items[i].jaql.dim) { //this is for normal filters

                                                                                    args.dashboard.filters.$$items[i].jaql.filter = {

                                                                                                    explicit: true,

                                                                                                    members:[result.values[x][0].data],

                                                                                                    multiSelection: false,

                                                                                                    userMultiSelect: false

                                                                                    };

                                                                    }

                                                    }

                                    },

                                    contentType: "application/json; charset=utf-8",

                                    dataType: "json"

                    });

    });

     

     

    Hope this gets you going in the right direction!

    0
  • David Oyler

    Thank you Elliot - I'll try this out.

    0
  • David Oyler

    I have the below working. This is at the Widget level and simply gets today's date and the last day of the month. I didnt need to query the date table as Elliot's script does.


    var defaultFilterConfig = {
        dim1: "[Date.Date (Calendar)]"
    };

    widget.on('initialized', function(d, args) {
        $.ajax({
            success: function(result) {
                            
                var date = new Date();
                var lastDay = new Date(date.getFullYear(), date.getMonth() + 1, 0);
                                        
                for(var i=0; i<args.widget.filters.$$items.length; i++) {
                    
                    if(defaultFilterConfig.dim1 === args.widget.filters.$$items[i].jaql.dim) { //this is for normal filters
                        args.widget.filters.$$items[i].jaql.filter = {
                            explicit: true,
                            from:(new Date()).toISOString().substring(0,10),
                            to:lastDay,
                            //members:[(new Date()).toISOString().substring(0,10), someDate],
                            multiSelection: false,
                            userMultiSelect: false
                        };
                    }
                }
            },
            contentType: "application/json; charset=utf-8",
            dataType: "json"

        });

    });

    0
  • Elliott Herz

    Hi David,

    If you don't have to query the filter dimension, you should remove the AJAX call. Glad you got it to work! Awesome job!

    0
  • Jessica Orlando

    Is there a way to use this code to filter for a specific date up until today? So today will always be the max date?

    1
  • Samuel Bassey

    Did you get a response to your question Jessica as I am trying to do exactly the same thing where the date "today" gets updated to current day in a widget.

    0
  • Jessica Orlando

    Hi Samuel, I did not get a response to my question and was not able to figure out a solution :(

    0

Please sign in to leave a comment.