How to get From and To date from range date filter to use in a formula measure

#1

Hello, I wanted to know how can I get the FROM and TO dates of the date range filter. What I want are two things.

  1. Get the amount of days between from and to , to use it in a formula measure.
  2. Get the amount of days between my date(on my table as a dimension) and the To date from the filter, to use it in another formula measure.

Thanks

Multiple Parameters
#2

I bet you there is a way to get that date via script.
I been looking for an example but did not find a perfect match.

Here the list of some sample related to filters, all of the set but you can get the value instead,
Script samples

Here is how to explore what is on your dashboard and find what to use in your script:
Developer tools

From what you stated this script would have to run any time the filter changed and on load of the dashboard, is that correct?

#3

Hi Carolina

This is quite advanced but as I see it, you have three stages here. This is how how I would do it, there may be a much simpler way. I hope there is.

  1. Get the dates from the filter. This can be done in a script.
  2. Doing the maths to work out the difference. This can be done in the script.
  3. Getting the result from stage 2 into a formula. This is done with a script and parameter placeholder.

Getting the dates is quite complicated as you need to query both upper and lower bounds of the parameter values, and then resolve the results to JavaScript date objects. This involves promises and call backs, and - although I have done it before - is not for the faint-hearted.

You can then use JavaScript date functions to work out the difference.

Then use the script to put the number into a parameter placeholder, to use in your formulas. See this article
https://www.dundas.com/support/learning/documentation/analyze-data/formulas/adding-formulas#h7-parameter-placeholders about how to create and use these in a formula.

The script would need to be on the value/parameter change interaction of the date range hierarchy picker.

Hope this points you in the right direction, but I hope even more that someone has an easier way.

1 Like
#4

On 2nd thoughts, it is only complicated if you allow for tokens, e.g. past 30 days. If you are only allowing proper dates it is much easier.

     currentView = this.baseViewService.currentView;		
     dateParam = currentView.control.getViewParameterByName("Date");
     from = dateParam.parameterValue.lowerBoundaryValue.memberTime;
     to = dateParam.parameterValue.upperBoundaryValue.memberTime;
1 Like
#5

Hi, Thanks for your help. But What I ended up doing is just doing the calculations on the cube (actually with a manual query with placeholders for the from and to date) And then created two separate single date filter and connected them to the placeholders ( if I tried to use a range date filter It didn’t let me connect it to the placeholders).

Thanks

#6

This is good advice. I’d also like to add to remember to always be very careful with dates in JS. Unfortunately JS doesn’t have good date support, especially with time-zones, and it can be rather confusing. To make it worse, by definition all dates are actually ranges because they lack granularity - for example if you want to see “Jan 1”, it’s technically “Jan 1 midnight to Jan 1 11:59:59”, so memberTime in this example will yield “Jan 1 midnight”, and the upperBoundaryTime would contain the “Jan 1 11:59:59” (assuming you wanted the day level). The only exception being stored procedure parameters which expect an explicit date/time slice (SingleDateTimeValue usually).

To further make this confusing, if that’s even possible, crafting a date in JS (new Date()) yields a local time which, if sent to the server directly, would suffer date-shifting. This is even more complex because not every browser necessarily offsets the date when created. It’s a mess.

In any case, in your particular example you’re referencing the property dundas.data.MemberValue.memberTime. This date will be in UTC (on purpose), and it is safe to send back to the server for filtering. However, often people will use the dundas.data.TimeHierarchyMember instead (or just HierarchyMember) as a result of a getMembers call, or just processing the dataResult on a particular DV. When a hierarchy member is used (not memberValue), the memberTime and upperBoundaryTime properties are actually local. This is useful for displaying in a DV, but useless for sending to the server since your times will all be shifted. As such, it’s important to always call loadMemberValue before sending it to the server which will automatically convert the local dates to UTC.

This is also (and 1 of a few reasons) why we highly recommend using the Calendar Control (over the date/time control), and working with Time Dimensions and hierarchy members as much as possible instead of ‘actual’ dates. I realize this may seem like more work at first, but the pay-off is that you don’t need to deal with any UTC/local offsetting or bad JS date handling, and never need to worry about time dimension mapping (fiscal or otherwise) as well as caption formatting. It’s a very big benefit, albeit with some up-front cost.

Lastly, in an upcoming version of Dundas BI next year you’ll have a much easier time working with tokens in the UI as we’ve implemented the ability to ‘resolve’ tokens to their real values.

1 Like
How to display the From and To dates on Tooltip from DATE range filter?
#7

Hello All,

Is anyone tried to display the From and To dates on Tooltip from DATE range filter?

What I want is to display the selected DATE range filter values on data point tooltip (both from and to date)

Thanks