Range Filter - need date difference on different levels (day, week, month, year)

#1

Hi guys,

I have the following situation:

On a dashboard with a range date filter, that can be set from the range of 1 day to the range of multiple years (with day granularity), I need to check if the difference between the two dates fits in any of these intervals:
1 week - 1 month - 3 months - 6 months - 1 year - All data
Depending on where the difference fits, I will need to hence grey and not be able to press the labels from the figure above (1W, 1M, 3M etc.), which are lower than where the value fits.

For example, if I choose a range from 01.01.2018 to 27.02.2018, it will fit between 1M - 3M, and I will have to hence gray the 1W and 1M labels and not be able to press them, in order to change the granularity of the chart.

Another thing is how to set the chart to be dynamically adjusted in order to arrange accordingly to the interval, when I press the labels above it.

e.g.: for the interval above, I will be able to select 3M, 6M, 1Y, etc. If I select 6M, I will have a chart with a 6 month range on bottom axis, starting from 01.01.2018 to 01.06.2018 and with a trend line that starts on 01.01.2018 and ends on 27.02.2018.

I know how to extract the dates from the filter, but after this I’m stuck.

image

Hope I’ve been pretty clear on what I need.

Any suggestions? Thank you in advance!

1 Like
#2

Hi Octavian,

You can leverage this sample script in your parameter date time value changed action:
//set constants for how many days in year or month
var year = 365.25;
var month = 31;
var week = 7;
var threeMonths = month * 3;
var sixMonths = month * 6;

//set script names of labels
var oneWeekLabel = label1week;
var oneMonthLabel = label1month;
var threeMonthsLabel = label3months;
var sixMonthsLabel = label6months;
var oneYearLabel = label1year;

//set view parameter script name
var vpName = “viewParameterDate”;

var baseViewService = this.getService(“BaseViewService”);
var view = baseViewService.currentView;

//get the view parameter
var vpDate = view.control.viewParameters.toEnumerable().first(function(vp) {
return vp.name === vpName;});

//Get the date difference in days
var dateDiff = Math.floor((vpDate.parameterValue.upperBoundaryValue - vpDate.parameterValue.lowerBoundaryValue) / (10006060*24));

//Check to see if the date difference is greater than 1 year. I used 365.25 as a measure for 1 year
if (dateDiff >= year){
//Set all labels to gray and disable mouse
inactive([oneYearLabel,sixMonthsLabel,threeMonthsLabel,oneMonthLabel,oneWeekLabel]);
}
else if (dateDiff >= sixMonths){
inactive([sixMonthsLabel,threeMonthsLabel,oneMonthLabel,oneWeekLabel]);
active([oneYearLabel]);
}
else if (dateDiff >= threeMonths){
inactive([oneYearLabel,threeMonthsLabel,oneMonthLabel,oneWeekLabel]);
active([sixMonthsLabel]);
}

else if (dateDiff >= month){
inactive([oneYearLabel,sixMonthsLabel,oneMonthLabel,oneWeekLabel]);
active([threeMonthsLabel]);
}
else if (dateDiff >= week){
inactive([oneYearLabel,sixMonthsLabel,threeMonthsLabel,oneWeekLabel]);
active([oneMonthLabel]);
}
else {
inactive([oneYearLabel,sixMonthsLabel,threeMonthsLabel,oneMonthLabel]);
active([oneWeekLabel]);
}

//set labels to inactive by setting the colour to Gray and ignoring mouse
function inactive(labels){
for (i=0; i<labels.length; i++) {
labels[i].fontColor = “Gray”;
labels[i].ignoreMouse = true;
}
}

//set labels to active by setting the colour to blue and ignoring mouse
function active(labels){
for (i=0; i<labels.length; i++) {
labels[i].fontColor = “DundasBlue”;
labels[i].ignoreMouse = false;
}
}

This will then change the font colors and ignore the mouse on the appropriate labels.

My sample:
sample1
sample2

1 Like
#3

Hi Steven,

Great script.
Thank you so much! This is very valuable!

Now all I need is to figure out how to push the lowerBoundaryValue of the parameter to the Custom Maximum property of the bottom axis of the chart, each time I interact with the filter values, so that the axis will change dynamically depending on the range selection…

#4

Hi Octavian,

You can add this line to the script if you want to set the bottom axis’s custom maximum to the lower boundary value of the parameter:
chart1.control.xAxes[0].maximum = vpDate.parameterValue.lowerBoundaryValue;

However, if you mean to set the custom maximum of the bottom axis’s custom maximum to the upper boundary value of the parameter, you can use this script:
chart1.control.xAxes[0].maximum = vpDate.parameterValue.upperBoundaryValue;

#5

Hi Steven,

Thank you again!
What I meant in the last comment (but wrote it wrong :slight_smile: ) was actually to insert in the custom maximum, the lower boundary value + the number of days/months associated with each label.

So if I press for e.g. 6M, it will take the lower value from the filter, add 6 month to it, then pass it to the custom maximum property.

Your last line of script helps a lot. I guess I will insert it on each label and adapt it accordingly to the period.

Many thanks!

#6

Hi all,

Any idea for a more precise solution.
Meaning that taking in consideration that a month has 31 days is not pretty accurate when the months in the interval have less than 31 days?

Thanks!

#7

Hi Octavian,

Can you provide some more information on how you want the months to be calculated?

For example if the start date is January 30th, what are your expected results for 1,3, and 6 months?

Are you thinking along the lines of doing a proration of (number of days used) / (number of days in month) until the sum = 1

So for January 30th, it’s 2/30 = 0.065
Then in February (1-0.65) * 28 = 26.18

So 1 month from January 30th would be February 26th?

#8

I found this on stack over flow
https://stackoverflow.com/questions/2536379/difference-in-months-between-two-dates-in-javascript

Octavian read that over and find what best fits what you are going for.
You might have to take bit from several suggestion to make you full solution. It also depends on what you decide on is a month, if they put in 1 February to 28 February is that a full month (for sure not on leap year).

Date math is always tricky when you only have a simple date structure to work with and not a class that has built into it a definition of what a month time frame is, because you then have define that yourself.

I guess my question is what is the definition of a month on the button called Month, 3 Months, 6 Months?
That is the same that you should be testing for before graying out the lower valued ones.

#9

I think something like this could work

var upperDate = parameterDateTime1.control.parameterValue.upperBoundaryValue;
var lowerDate = parameterDateTime1.control.parameterValue.lowerBoundaryValue;

var year = 365;
var week = 7;
// Leap Year
if (lowerDate.getFullYear() % 4 == 0) {
  year = 366;
}

var monthDate = new Date (upperDate.getTime()); // Create copy of date so that upperDate variable isn’t referenced
monthDate.setMonth(monthDate.getMonth() - 1); // Go back one month
var month = Math.floor((upperDate.getTime() - monthDate.getTime()) / (1000*60*60*24)); // Get days in between the interval

var threeMonthDate = new Date (upperDate.getTime()); // Repeat for next three and six month intervals
threeMonthDate.setMonth(threeMonthDate.getMonth() - 3);
var threeMonths = Math.floor((upperDate.getTime() - threeMonthDate.getTime()) / (1000*60*60*24));

var sixMonthDate = new Date (upperDate.getTime());
sixMonthDate.setMonth(sixMonthDate.getMonth() - 6);
var sixMonths = Math.floor((upperDate.getTime() - sixMonthDate.getTime()) / (1000*60*60*24));

This would give you a lot more precision as it takes into account leap years and months.

#10

Have you tested that on a Feb 02 to March 3rd time frame?
Does it say it is a month?
I am running it now.
I just tested it with:
var upperDate = new Date(“2019/04/05”);
var lowerDate = new Date(“2019/03/22”);

and it gave 30 for month, but this time range is not a month long

Also i did:
var upperDate = new Date(“2019/02/28”);
var lowerDate = new Date(“2019/02/01”);
and it said a month was 31 days (last month).

I think we are the right track here with getting time frames. because if i do march 1 and 31 then i get 27 days (close it should be 28).

So maybe not do the minus?
Time for lunch I think on this one…