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”
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)
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:
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:
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:
