Dynamic Offset For Date Filters
Introduction:
The following script will allow you to get the functionality of "Last x days" for any chosen date in the dashboard filter.
Steps:
1. create a dashboard filter of type "days" and set it up to be a radio button:
2. Paste the following in the dashboard script:
var TABLE = "sales";
var COLUMN = "date";
var COUNT = 90;
dashboard.on("widgetbeforequery", function(d, a) {
// getting item
var item = a.query.metadata.find(function(i) { return i.panel === "scope" && $$get(i, "jaql.table") === TABLE && $$get(i, "jaql.column") === COLUMN });
if (!defined(item)) {
console.log("DynamicLastX: Can't find a filter item with '" + TABLE + "." + COLUMN +"' dimension. Aborting.");
return;
}
// getting member
var members = $$get(item, "jaql.filter.members");
if (!defined(members) || members.length !== 1) {
console.log("DynamicLastX: Expecting single member filter. Aborting.");
return;
}
// replacing members with last/next
delete item.jaql.filter.members;
item.jaql.filter.last = {
offset: 0,
count: COUNT,
anchor: members[0]
};
});
3. Edit the variables at the beginning of the script.
(COLUMN: The field used in the dashboard filter
TABLE: the table that contain that field in the ElastiCube
COUNT: the number of days preceding the chose date)
var TABLE = "sales";
var COLUMN = "date";
var COUNT = 90;Updated 03-02-2023
