Chart with dynamic range

jaitsujin · March 23, 2020, 3:18am

I was wondering if it is possible to implement a chart with a year or month selection.

Let say i have a list with data for the last 5 years.
I need a chart that initial shows all data, but gives you the option to filter down by year or month.

Ideally something interactive and aesthetically pleasing like this.

Is this something that is possible?

jaitsujin · March 23, 2020, 5:15am

I was able to come up with some of the prep work to populate the buttons dynamically, but got kinda stuck on how to reload the chart once one of these buttons has been clicked. Any help is appreciated.

// DATA SOURCE - ADVANCED

handlers.requestSuccess = function (data, logger, el) {

    availableYears = [];

    for (var i = 0; i < data.items.length; i++) {
        var year = data.items[i].Date.getFullYear();
        if (availableYears.indexOf(year) === -1) {
            availableYears.push(year);
        }
    }

    logger.debug('array: ', availableYears);
    return true;
}

// DASHBOARD - ADVANCED

var handlers = {};

handlers.preRender = function (config, logger, processor, el) {

    el.parent().prepend('<p id="buttons" style="text-align: center">Filter by Year: </p>');

    for (var i = 0; i < availableYears.length; i++) {
        var btn = document.createElement("button");
        var t = document.createTextNode(availableYears[i]);
        btn.appendChild(t);
        document.getElementById('buttons').appendChild(btn);
    }

    return true;
}

Margo · March 24, 2020, 6:39am

Hello @jaitsujin,

Great start!

You need to create buttons with onclick event, so when the user clicks on an element run the function, that will filter data and the chart.

    for (var i = 0; i < availableYears.length; i++) {
        var btn = document.createElement("button");
        var t = document.createTextNode(availableYears[i]);
        btn.appendChild(t);
        //add oncklick event
        btn.onclick = myFunction;
        document.getElementById('buttons').appendChild(btn);
    }

You can find the example of the dynamic filtering the documentation here.

jaitsujin · March 24, 2020, 10:38pm

Hi,
This looks promising. I'll give it a try now. Thank you.

jaitsujin · March 25, 2020, 12:56am

Thanks for pointing my in the right direction.

I got it narrowed down to the following:

// DATA SOURCE - ADVANCED

// REQUEST INIT
handlers.requestInit = function (query, logger) {
    var view = query.get_viewXml();

    view = view.replace('{Filter}', '<And>\
            <Geq>\
                <FieldRef Name="Date" />\
                <Value Type="DateTime">2000-01-01T00:00:00Z</Value>\
            </Geq>\
            <Leq>\
                <FieldRef Name="Date" />\
                <Value Type="DateTime">2100-12-31T23:59:59Z</Value>\
            </Leq>\
            </And>');

    logger.info(view);
    query.set_viewXml(view);

    return true;
}

// REQUEST SUCCESS
    availableYears = [];
    handlers.requestSuccess = function (data, logger, el) {

        for (var i = 0; i < data.items.length; i++) {
            var year = data.items[i].Date.getFullYear();
            if (availableYears.indexOf(year) === -1) {
                availableYears.push(year);
            }
        }
        logger.debug('array: ', availableYears);
        
        // filterYears();

        return true;
    }

// AGGREGATION FINISH
handlers.finish = function (query, data, logger, processor) {
    $('body').on('click', '.yearSelectors', function () {

        var thisYear = $(this).text();
        console.log(thisYear);

        var view = query.get_viewXml();
        view = view.replace('{Filter}', '<And>\
                <Geq>\
                    <FieldRef Name="Date" />\
                    <Value Type="DateTime">' + thisYear + '-01T00:00:00Z</Value>\
                </Geq>\
                <Leq>\
                    <FieldRef Name="Date" />\
                    <Value Type="DateTime">' + thisYear + '-12-31T23:59:59Z</Value>\
                </Leq>\
                </And>');
        logger.info(view);
        query.set_viewXml(view);

    });

    return true;
}

// DASHBOARD - ADVANCED

    // STYLE
var css = '.yearSelectors { background: #eee; color: #464646; font-size: 17px; border: 1px solid #464646; padding: 5px 20px; margin: 2px; cursor: pointer;} .yearSelectors:hover{ background: #c32032; color: white;}',
    head = document.head || document.getElementsByTagName('head')[0],
    style = document.createElement('style');

head.appendChild(style);

style.type = 'text/css';
style.appendChild(document.createTextNode(css));

// SCRIPT
var handlers = {};
handlers.preRender = function (config, logger, processor, el) {

    logger.debug('Configuration: ', config);
    // logger.debug('Processor: ', el);
    // console.log(availableYears);


    el.parent().prepend('<p id="buttons" style="text-align: center;">Filter by Year: </p>');

    for (var i = 0; i < availableYears.length; i++) {
        var btn = document.createElement("span");
        btn.setAttribute("class", "yearSelectors");
        var t = document.createTextNode(availableYears[i]);
        btn.appendChild(t);
        //add oncklick event
        btn.onclick = filterYears;
        document.getElementById('buttons').appendChild(btn);
    }

    function filterYears(config) {
      // console.log('filterYears');
    }

    return true;
}

Which returns the follwoing

Now I'm stuck on how i can refresh the chart dynamically to apply the new CAML query when selecting one of the years. The onclick function works, but query doesn't seem to be available anymore

Currently getting the following error:

Any help is greatly appriciated.

jaitsujin · March 25, 2020, 7:30am

Me again,

I came up with another solution that seems to be working exactly as I wanted it to.

On load all list data is pulled into the chart, and year filters are populated dynamically based on available data.

Page loaded

image1681×416 9.42 KB

Filtere applied

image1674×408 9.76 KB

To achieve this i did the following. Custom List, 3 Columns(City, Spending, Date)

// DATA SOURCE - ADVANCED

// REQUEST SUCCESS
availableYears = [];
handlers.requestSuccess = function (data, query, logger, el) {

    for (var i = 0; i < data.items.length; i++) {
        var year = data.items[i].Date.getFullYear();
        if (availableYears.indexOf(year) === -1) {
            availableYears.push(year);
        }
    }
    // logger.debug('array: ', availableYears);

    availableYears.sort();
    return true;
}

// FINISH
handlers.finish = function (query, data, logger, processor) {
    $('body').on('click', '.yearSelectors', function () {
        var selectedYear = $(this).text();
        // Refresh Function
        refresh(query, data, logger, selectedYear);
    });

    return true;
}

// FUNCTION
function refresh(query, data, logger, selectedYear) {

    // Get Chart
    var chart = $("[id*='chartg']").data("kendoChart");

    // Get all data;
    var series = query;

    // Create Date Range
    var startDate = new Date("01/01/" + selectedYear);
    var endDate = new Date("12/31/" + selectedYear);

    // Filter by Date Range
    var filteredSeries = series.items.filter(row => row.Date >= startDate && row.Date <= endDate);

    // Apply filtered Data Source
    var dataSource = new kendo.data.DataSource({
        data: filteredSeries
    });
    chart.setDataSource(dataSource);

}

// DASHBOARD - ADVANCED

// STYLE
var css = '.yearSelectors { background: #eee; color: #464646; font-size: 17px; border: 1px solid #464646; padding: 5px 20px; margin: 2px; cursor: pointer;}\
           .yearSelectors:hover{ background: #c32032; color: white;}\
           .yearSelectorsSelected { background: #464646 !important; color: #fff;}',
    head = document.head || document.getElementsByTagName('head')[0],
    style = document.createElement('style');

head.appendChild(style);

style.type = 'text/css';
style.appendChild(document.createTextNode(css));

// SCRIPT
var handlers = {};
handlers.preRender = function (config, logger, processor, el) {

    logger.debug('Configuration: ', config);

    el.parent().prepend('<p id="buttons" style="text-align: center;">Filter by Year: </p>');

    // Create Buttons
    for (var i = 0; i < availableYears.length; i++) {
        var btn = document.createElement("span");
        btn.setAttribute("class", "yearSelectors");
        var t = document.createTextNode(availableYears[i]);
        btn.appendChild(t);
        btn.onclick = filterdYear;
        document.getElementById('buttons').appendChild(btn);
    }

    function filterdYear() {
            $(this).addClass('yearSelectorsSelected');
            $('.yearSelectors').not($(this)).removeClass('yearSelectorsSelected');
    }

    return true;
}

Again, this works perfectly fine, but I have one last problem that I can't seem to wrap my head around. I put this together in Chrome and it works just fine (same for Edge) but i'm getting the following error in IE. (Yes, we do need to support IE in our company ).

Pre-rendering failed: ReferenceError: 'availableYears' is undefined

I remember having to add
window.data = data;
But this didn't work this time, or i set t up incorrectly.

Do you happen to know how to fix this?

Thank you for everything!

Margo · March 25, 2020, 10:39am

Hello @jaitsujin,

Great job!

Please try to make availableYears a global variable:

window.availableYears = [];

And replace availableYears with window.availableYears.

Let me know if that helped to make it work in IE.

jaitsujin · March 27, 2020, 12:09am

Hi, thanks for the response.

It took me one step closer, but did not work.

I've addedd the following line to Dashboard > Advanced

window.availableYears = ;

And appended window. to my for loop.

// SCRIPT
window.availableYears = [];
var handlers = {};
handlers.preRender = function (config, logger, processor, el) {

    logger.debug('Configuration: ', config);

    el.parent().prepend('<p id="buttons" style="text-align: center;">Filter by Year: </p>');

    // Create Buttons
  	console.log("availableYears: " + window.availableYears);
    for (var i = 0; i < window.availableYears.length; i++) {
        var btn = document.createElement("span");
        btn.setAttribute("class", "yearSelectors");
        var t = document.createTextNode(window.availableYears[i]);
        btn.appendChild(t);
        btn.onclick = filterdYear;
        document.getElementById('buttons').appendChild(btn);
    }

    function filterdYear() {
            $(this).addClass('yearSelectorsSelected');
            $('.yearSelectors').not($(this)).removeClass('yearSelectorsSelected');
    }

    return true;
}

Console logs blank array.

Am I adding this the wrong way? or what am I forgetting?

Still working in Chrome,

but not IE

Nikita_Kurguzov · March 27, 2020, 12:33pm

Dear @jaitsujin,
You need to only define availableYears in Data Source -> Advanced:

window.availableYears = [];

Then, keep using window.availableYears everywhere, instead of availableYears.

Finally, make sure NOT to use this line again in Dashboard > Advanced:

window.availableYears = []; //this is NOT needed!

If you include it again, it will simply overwrite existing data and will return an empty array.

jaitsujin · March 30, 2020, 6:36am

Hi,

I've tried your suggestion, but with no luck. My current setup is as follows.

Dashboard > Advanced

var handlers = {};
// INIT
handlers.init = function (data, logger) {
return true;
}

// REQUEST INIT
handlers.requestInit = function (query, logger) {
    return true;
}

// REQUEST SUCCESS
handlers.requestSuccess = function (data, query, logger, el) {
window.availableYears = [];
   // window.data = data;
   // window.query = query;

    for (var i = 0; i < data.items.length; i++) {
        var year = data.items[i].Date.getFullYear();
        if (window.availableYears.indexOf(year) === -1) {
            window.availableYears.push(year);
        }
    }
    // console.log('array: ' + window.availableYears);

    window.availableYears.sort();

    return true;
}

// REQUEST ERROR
handlers.requestError = function (error, logger) {
    return $.Deferred().reject(error);
}

// AGGREGATION SUCCESS
handlers.aggregationSuccess = function (data, query, logger) {
  window.data = data;
    return true;
}

// AGGREGATION ERROR
handlers.aggregationError = function (error, logger) {
    return $.Deferred().reject(error);
}

// AGGREGATION FINISH
handlers.finish = function (query, data, logger, processor) {
    $('body').on('click', '.yearSelectors', function () {
        var selectedYear = $(this).text();
        // Refresh Function
        refresh(query, data, logger, selectedYear);
    });

    return true;
}

// FUNCTIONS
function refresh(query, data, logger, selectedYear) {

// Get Chart
    var chart = $("[id*='chartg']").data("kendoChart");

// Get all data;
    var series = query;

// Create Date Range
    var startDate = new Date("01/01/" + selectedYear);
    var endDate = new Date("12/31/" + selectedYear);

// Filter by Date Range
    var filteredSeries = series.items.filter(row => row.Date >= startDate && row.Date <= endDate);

// Apply filtered Data Source
    var dataSource = new kendo.data.DataSource({
    data: filteredSeries
    });
    chart.setDataSource(dataSource);
}

Dashboard > Advanced

// STYLE
var css = '.yearSelectors { background: #eee; color: #464646; font-size: 17px; border: 1px solid #464646; padding: 5px 20px; margin: 2px; cursor: pointer;}\
           .yearSelectors:hover{ background: #c32032; color: white;}\
           .yearSelectorsSelected { background: #464646 !important; color: #fff;}',
    head = document.head || document.getElementsByTagName('head')[0],
    style = document.createElement('style');

head.appendChild(style);

style.type = 'text/css';
style.appendChild(document.createTextNode(css));

// SCRIPT
var handlers = {};
handlers.preRender = function (config, logger, processor, el, window) {

logger.debug('Configuration: ', config);

el.parent().prepend('<p id="buttons" style="text-align: center;">Filter by Year: </p>');

// Create Buttons
console.log("availableYears: " + window.availableYears);
for (var i = 0; i < window.availableYears.length; i++) {
    var btn = document.createElement("span");
    btn.setAttribute("class", "yearSelectors");
    var t = document.createTextNode(window.availableYears[i]);
    btn.appendChild(t);
    btn.onclick = filterdYear;
    document.getElementById('buttons').appendChild(btn);
}

function filterdYear() {
        $(this).addClass('yearSelectorsSelected');
        $('.yearSelectors').not($(this)).removeClass('yearSelectorsSelected');
}

return true;

}

Now I'm getting the following error.
Pre-rendering failed: TypeError: Cannot read property 'availableYears' of undefined

Any ideas?

Nikita_Kurguzov · March 30, 2020, 6:59am

Dear @jaitsujin,
Yes, please, don't pass window as one of function's arguments... You don't need it there, window is a global variable. Doing this simply creates a new variable, which will be undefined.

Instead of this:

handlers.preRender = function (config, logger, processor, el, window) {

simply use:

handlers.preRender = function (config, logger, processor, el) {

jaitsujin · March 30, 2020, 10:48pm

Hi,

That makes sense. I removed it, but now I'm getting the following. Only in IE again, Chrome and Edge work just fine.

Error in IE

Data Source > Advanced

Dashboard > Advanced

image879×650 19.7 KB

Nikita_Kurguzov · March 31, 2020, 8:50am

Dear @jaitsujin,
Very strange... I would ask you to try and remove the rest of the code from Dashboard -> Advanced and leave just this (copy the old code somewhere):

var handlers = {};
handlers.preRender = function(config, logger) {
  console.log("Available years: ");
  console.log(window.availableYears);
  return true;
}

jaitsujin · March 31, 2020, 3:07pm

Hi. I tried your suggestion. These are the logs for IE and Chrome
IE

Chrome

No luck.

I reviewed everything and moved a few things around and came up with the following.

Data Source > Advanced

window.availableYears = [];
handlers.requestSuccess = function (data, logger) {


    for (var i = 0; i < data.items.length; i++) {
    var year = data.items[i].Date.getFullYear();
    if (window.availableYears.indexOf(year) === -1) {
        window.availableYears.push(year);
    }
}
window.availableYears.sort();

console.log(window.availableYears);

    return true;
}

handlers.finish = function (query, data, logger) {

    $('body').on('click', '.yearSelectors', function () {
    // Get Year
    var selectedYear = $(this).text();
    // Get Chart
    var chart = $("[id*='chartg']").data("kendoChart");

    // Get all data;
    var series = query;

    // Create Date Range
    var startDate = new Date("01/01/" + selectedYear);
    var endDate = new Date("12/31/" + selectedYear);

    // Filter by Date Range
    var filteredSeries = series.items.filter(row => row.Date >= startDate && row.Date <= endDate);

    // Apply filtered Data Source
    var dataSource = new kendo.data.DataSource({
        data: filteredSeries
    });
    chart.setDataSource(dataSource);

});


    return true;
}

Dashboard > Advanced

// STYLE
var css = '.yearSelectors { background: #eee; color: #464646; font-size: 17px; border: 1px solid #464646; padding: 5px 20px; margin: 2px; cursor: pointer;}\
           .yearSelectors:hover{ background: #c32032; color: white;}\
           .yearSelectorsSelected { background: #464646 !important; color: #fff;}',
    head = document.head || document.getElementsByTagName('head')[0],
    style = document.createElement('style');

head.appendChild(style);

style.type = 'text/css';
style.appendChild(document.createTextNode(css));

// SCRIPT
var handlers = {};
handlers.preRender = function (config, logger, processor, el) {

el.parent().prepend('<p id="buttons" style="text-align: center;">Filter by Year: </p>');

// Create Buttons
console.log("availableYears: " + window.availableYears);
for (var i = 0; i < window.availableYears.length; i++) {
    var btn = document.createElement("span");
    btn.setAttribute("class", "yearSelectors");
    var t = document.createTextNode(window.availableYears[i]);
    btn.appendChild(t);
    btn.onclick = filterdYear;
    document.getElementById('buttons').appendChild(btn);
}

function filterdYear() {
    $(this).addClass('yearSelectorsSelected');
    $('.yearSelectors').not($(this)).removeClass('yearSelectorsSelected');
}

   return true;
}

This works also great in Chrome, but again not IE.

When i remove the jquery onclick function everything renders good in IE. Do i have to place it somewhere else?

Nikita_Kurguzov · March 31, 2020, 3:27pm

Dear @jaitsujin,
The only problem that we can spot, that can give an error in IE is this part:

function filterdYear() {
    $(this).addClass('yearSelectorsSelected');
    $('.yearSelectors').not($(this)).removeClass('yearSelectorsSelected');
}

Since it is used in onclick function, it could be the reason you're getting the error. It needs to be rewritten, without this in it! It might be confusing the IE.

Try something like that maybe:

function filterdYear(el) {
    $(el).addClass('yearSelectorsSelected');
    $('.yearSelectors').not($(el)).removeClass('yearSelectorsSelected');
}

jaitsujin · April 1, 2020, 3:15am

Hi all,

I finally got it. Yay!

the filteredYear function was actually ok. It was the filter that i was trying to apply to data coming in.

var filteredSeries = series.items.filter(row => row.Date >= startDate && row.Date <= endDate);

I had to write it out like this and everything works in all browsers.

var filteredSeries = [];
   for (var i = 0; i < series.items.length; i++) {
      if (series.items[i].Date >= startDate && series.items[i].Date <= endDate) {
          filteredSeries.push(series.items[i]);
      }
}

Now I'm not sure how quick it will run when i have more data to parse. But at least i know now why it didn't work. Thanks for all the help regardless.

Last question in regards to best practices. Do you think something like this works in the long run?
Or would you have approached this requirement differently?

Thanks!

Nikita_Kurguzov · April 2, 2020, 9:09am

Dear @jaitsujin,
Shouldn't be too big of an issue, although you should still be able to use filter, just don't use the =>, instead use functions like this:

var filteredSeries = series.items.filter(function(row){
   return row.Date >= startDate && row.Date <= endDate;
});

jaitsujin · April 2, 2020, 8:34pm

That worked like a charm. Thanks!

jaitsujin · April 3, 2020, 5:33am

Would you happen to know if it is possible to pass a variable into a loop?

Instead of this

for (var i = 0; i < query.items.length; i++) {
        console.log(query.items[i].Date.getFullYear());
}

I would need something like this

for (var i = 0; i < query.items.length; i++) {
        console.log(query.items[i].**varDate**.getFullYear());
}

Is that even possible?