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;
-
Hi Lian,
I've attempted to use your code to set a from/to date filter, using today's date for the to parameter (a dynamic year to date).
Similar to:
define this:
var TODAY = moment().format("YYYY-MM-DD");
then:
item.jaql.filter= {
from: "2017-01-01",
to: TODAY
};or
item.jaql.filter.from= "2017-01-01";
item.jaql.filter.to= TODAY;1) I get an error with the "find" method:
- TypeError: Object doesn't support property or method 'find'
So it could be that the above code is ok, but never gets called so I can't find out
2) Is this how I can set the filter in this way?
3) Alternatively, is there a way to do this within a JAQL filter, similar to (which also doesn't work):
{
"from": "2017-01-01",
"to": "2017-02-16"
}Appreciate any help.
Thanks,
Andy
-
Hi Andy,
Your question is almost a year old, but I may have an answer for you.
The error you have on the find method is not normal as Lian's script works and do not throuw this error. As this part is not changed in your attempt, you should not get error. Check carefully the syntax (parenthesis + ; at the end of each line).
What I do is transform a filter that is only "from dd/mm/yyyy" into "from dd/mm/yyy to today". I need the filter to exist first on the dashboard, and then I modify it. My script is :
var TABLE = "Dates";
var COLUMN = "Date";
var MAINTENANT = moment().format("YYYY-MM-DD");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 });
item.jaql.filter.to = MAINTENANT;
});Best,
David.
Please sign in to leave a comment.
Comments
6 comments