Dynamic offset for date filters

Follow

Lian

June 23, 2016 12:07

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;

1

• 

  yaron schwarzman November 10, 2016 16:45

  hi Lian

   

  Thanks for your help.

  Is there a way to modify this dashboard filter so it will use as a date hierarchy filter?

  I mean that the use will have the option to choose day/month/quarter/year (depends of the level of date he wants to see)?

  thanks

  Yaron

  0

  Comment actions Permalink
• 

  Andy Chan February 16, 2017 15:57

  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

   

   

  0

  Comment actions Permalink
• 

  David Hertzberg January 18, 2018 23:38

  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.

  1

  Comment actions Permalink
• 

  David Hertzberg January 18, 2018 23:40

  I still get an issue making this work perfectly : the filter displayed into the dashboard is not updated. So I start with a filter "from" and I would like it to render the real filter that is applied. Is it possible ?

  thanks,

  David.

  0

  Comment actions Permalink
• 

  Andy Chan January 19, 2018 16:13

  Hi David,

  Thanks for taking the time to reply!

  I will try this and see if it works for me.

  Regards,

  Andy

   

  0

  Comment actions Permalink
• 

  Dima Kruchenetsky May 29, 2019 16:11

  Hi, 

  i want to apply the same thing on a pivot table with 2 values. means that the dynamic date filter should work on each member of the pivot. how do i use the code for in this case? 

  thanks, 

  0

  Comment actions Permalink

Please sign in to leave a comment.