Custom style for aggregated measure range slider filter widget

Usage:

Filtering your Dashboard visuals by an aggregated measure (i.e. Sum of Sales)

Range slider code:

return function() {

    var f = function() {};

    f.version = "1";
    
    f.afterDataRequest = function(req) {
      // Get request object from filter widget
      var request = JSON.parse(req.dsreq());
      // Override distinct select expression to do an aggregation instead to find the total sum of the measure, 
      //  which will represent the max value on the slider range filter
      var aggregatesExpression = request["aggregates"][0]["expr"];
      var parameterName = aggregatesExpression.match(/\[(.*)\]/i)[1];
      var updatedaggregatesExpression = {expr: "sum(" + parameterName + ") as '"+ parameterName + "'" , order: {asc: true}};
      request["aggregates"][0] = updatedaggregatesExpression
      request["distinct"] = false;
      // Reset the request object
      req.dsreq(request);
      return req;
    };

    f.disableDraw = function() {
        return true;
    };
    
    f.beforeDraw = function() {
      // Remove existing filter dropdown when custom style is applied
      if ($("#" + arcapi.chartId() + " .selected-control-wrapper").length > 0 ){
        $("#" + arcapi.chartId() + " .selected-control-wrapper").remove();
      }
    };

    f.afterDraw = function() {
        // Get a jQuery object representation of the visual DOM object
        // INFO: Syntax breakdown :-
        //       $  : a dollar sign to define / access jQuery
        //       () : a (selector) to "query (or find)" HTML elements
        //       #  : the hash symbol prefix directs jQuery to search for HTML elements with the matching id attribute
            
        var $sliderFilter = $('#' + arcapi.chartId());
        
        // Get visual attributes to re-use in this new filter widget
        var picklistTitle = arcapi.unsupportedDriver.viz().picklist_title.value();
        var picklistWidth = arcapi.unsupportedDriver.viz().picklist_width.value()

        // Get the underlying data object
        var data = arcapi.dataResult();
        // Get the column from the data object
        var filterColumn = data.columns();
        var filterColumnName = filterColumn[0].colname();
        
        if($("#slider-filter-" + arcapi.chartId()).length === 0) {

            // Get the rows from the data object
            var stringRows = data.rows();
            var rows = []
            stringRows.forEach(function(row) {
              var newrow = [Number(row)]
              rows.push(newrow)
            })
            
            // Use d3 to get the min and max values for the row data
            // var rowMin = Math.round(d3.min(rows, function(d) { return d[0]; }));
            var rowMin = 0;
            console.log("INFO: Min : " + rowMin);
            var rowMax = Math.round(d3.max(rows, function(d) { return d[0]; }));
            console.log("INFO: Max : " + rowMax);
            var minParamName = `${filterColumnName}.min`;
            var maxParamName = `${filterColumnName}.max`;
            
            // Create html element for slider filter
            $sliderFilter.append('<div class="selected-control-wrapper">' +
                                 '    <div class="selected-control-title">' +
                                 '        <i class="fa fa-list"></i>' +
                                '        <span class="base_field">' + picklistTitle + '</span>' +
                                 '    </div>' +
                                 '    <div id="slider-filter-' + arcapi.chartId() + '" style="display: inline-block; width: ' + picklistWidth + 'px;">' +
                                 '        <input id="slider-range-min-' + arcapi.chartId() + '" style="text-align: center; width: 15%;"></input>' +
                                 '        <div id="slider-range-' + arcapi.chartId() + '" style="display: inline-block; width: 58%; margin-left: 3%; margin-right: 3%; vertical-align: middle;">' +
                                 '        </div>' +
                                 '        <input id="slider-range-max-' + arcapi.chartId() + '" style="text-align: center; width: 15%;"></input>' +
                                 '    </div>'+
                                 '</div>');

            // Create slider range function and events
            $("#slider-range-" + arcapi.chartId()).slider({
                    range: true,
                    min: rowMin,
                    max: rowMax,
                    values: [rowMin, rowMax],
                    slide: function(event, ui) {
                        $("#slider-range-min-" + arcapi.chartId()).attr('value', ui.values[0]);
                        $("#slider-range-min-" + arcapi.chartId())[0]["value"] = ui.values[0];
                        $("#slider-range-max-" + arcapi.chartId()).attr('value', ui.values[1]);
                        $("#slider-range-max-" + arcapi.chartId())[0]["value"] = ui.values[1];
                    },
                    stop: function(event, ui) {
                        console.log("INFO: ===> Slider stopped at range [" + ui.values[0] + " -> " + ui.values[1] + "]");
                        // Broadcast new column parameter min max values after sliding
                        var params = {}
                        params[minParamName] = ui.values[0];
                        params[maxParamName] = ui.values[1];
                        arcapi.sendParameters(params);
                    }
            });
            
            $("#slider-range-min-" + arcapi.chartId()).change(function () {
              // Broadcast new column parameter min max values after sliding
              var params = {}
              params[minParamName] = $(this)[0]["value"]
              $("#slider-range-min-" + arcapi.chartId()).attr('value', $(this)[0]["value"]);
              $("#slider-range-" + arcapi.chartId()).slider("values", 0, $(this)[0]["value"]);
              params[maxParamName] = $("#slider-range-" + arcapi.chartId()).slider("values", 1);
              arcapi.sendParameters(params);
            })
            
            $("#slider-range-max-" + arcapi.chartId()).change(function () {
              // Broadcast new column parameter min max values after sliding
              var params = {}
              params[maxParamName] = $(this)[0]["value"]
              $("#slider-range-max-" + arcapi.chartId()).attr('value', $(this)[0]["value"]);
              $("#slider-range-" + arcapi.chartId()).slider("values", 1, $(this)[0]["value"]);
              params[minParamName] = $("#slider-range-" + arcapi.chartId()).slider("values", 0);
              arcapi.sendParameters(params);
            })
            
            // Initialize slider range
            $("#slider-range-min-" + arcapi.chartId()).attr('value', $("#slider-range-" + arcapi.chartId()).slider("values", 0));
            $("#slider-range-max-" + arcapi.chartId()).attr('value', $("#slider-range-" + arcapi.chartId()).slider("values", 1));
            
            // Initialize slider column parameter values
            var params = {}
            params[minParamName] = $("#slider-range-" + arcapi.chartId()).slider("values", 0);
            params[maxParamName] = $("#slider-range-" + arcapi.chartId()).slider("values", 1);
            arcapi.sendParameters(params);

        }
    };

    return f;

}();

Applying the custom style:

Step 1: Go to the Settings menu in the top right and select “Custom Styles”

Step 2: Click “New Custom Style”

40%20PM

Step 3: Add the custom Javascript code for the range filter (see above)

Step 4: Go back to your Dashboard, and add the Measure column you’re interested in using the range filter with (i.e. sales)

22%20PM

Step 5: Click on the settings icon of the filter widget, and select the “Custom Style” tab, and click “Add Style”

Step 6: Select your custom range filter style (i.e. agg_measure_slider_filter), and click “Apply”

Now your custom style should be applied and you should see the range slider appear in place of the original dropdown filter:

Depending on the max value of your Measure you may need to increase the size of your filter widget in order to see the entire values in the range:

31%20PM

Enabling the range slider to filter your Dashboard visuals:

As you slide the range filter, you’ll notice that a min and max value for that filter are broadcasted into your Dashboard as parameters:

11%20PM

In order to apply these lower and upper bounds from the filter, we need to add these parameters names in a filter expression in each Visual in the dashboard to allow for dynamically updating of the Visuals. Take a look at this training video for more information on using parameters.

Step 1: Open each of your visuals you want to be filter by the range slider, add your Measure (i.e. Sales) to the filter shelf of your visual, and click “Enter/Edit Expression”:

Step 2: Create an expression that will filter your aggregated Measure by the values selected within the range slider:

sum([sales]) between <<sales.min:0>> and <<sales.max:100>>

Now when you slide the range filter you should see your Visuals update dynamically:

Before:

After:

1 Like