Dynamic Offset For Date Filters

[intapiuser]

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;