How best to implement selection of range of dates.
The data model our QV app consists of a fact table and several dimensional tables, one of which is a calendar table containing numeric date/timestamps.
When our client wants to view data of a specific date, we employ a QV Calendar object (that automatically converts the numeric values to something more date-like) which in turn sets a variable that our charts then use via Set Analysis to "filter" the desired data.
One of the main reasons we go the Set Analysis route instead of direct "List box" date selection is that if our client wants to view data over an arbitrary range of dates, we can employ two QV Calendar objects, one for the start date and one for the end date, and then use very simple Set Analysis to include data from every date in between the selected values. Otherwise, I think, we would have to require the client to manually select all of the dates for the desired range, and that's something the client refuses to do.
My question is this: is there a way (perhaps with a macro?) that would allow us to take the start and end dates specified in the Calendar objects' variables and automatically perform the in-model selection of the date values between those specified (inclusive) so that we can do away with the Set Analysis?
Just something that came to mind know, there is a possibility to select date ranges in list boxes too. By typing something like: >=01/07/2013<=15/07/2013 in the search box you would select a range of dates, although I appreciate that this requires some "user training" or may not be as intuitive as the calendar approach.
About your question, I think using set analysis with variables is way better than creating a macro for it due to better performance and compatibility across multiple platforms.
See, first thing is, we are going to play with variables and triggers.
Let us assume the date field in ur cal is ORD_DATE.
Take a var say vStartDate=Date({1}Min(ORD_DATE))
vEndDate=Date({1}Max(ORD_DATE))
(Anyhow the above var gets changed to a ordinary date, wen v select dates in calander, i ll explain abt it later)
Take a cal object, select the var. vStartDate
Set the min val as, Date({1}Min(ORD_DATE))
max val as, Date($(vEndDate))-1
Likewise for vEndDate,
And u know now set it for Min and Max val.
Now go to Triggers. (Ctrl+Alt+D)
In the Field Trigger of ORD_DATE,
*)Add OnChange Trigger, Set var, vStartDate
=Date({1}Min(ORD_DATE))
vEndDate
=Date({1}Max(ORD_DATE))
The above process is for "If select the clear button, the vStartDate must go to the starting Date of the ORD_DATE and for the vice versa for vEnd Date"
Now in ur charts expression whatever the stuff is, use ur vStartDate and vEndDate
Explanation abt var gets changed to a ordinary date, wen v select dates in calander.
It Should be changed, wen v select Dates, then only the exp and charts work according to our selection.
With regards to your first suggestion, unfortunately our client will not move away from the "pick two dates from two calendars" paradigm, so asking them to enter an expression in a search field is not viable.
As for the performance issue, I'll accept your expert opinion regarding macro performance as I have written no macros myself.
Still, the whole reason we want to move away from specifying date ranges within Set Analysis, is to attain better chart performance by using less Set Analysis and have it applied to a model that is already "reduced" through direct date selection within the model. (I hope I explained that correctly).
The way I understand it is that Set Analysis is like a filter that is applied over the entire model and if you can reduce the apparent size of the model by applying direct field selections, then charts render faster. Is this not true?
I understand your concern, to be honest I've never thought about it in that way.
I usually would go for the same approach as Jagan, although I think you may have a point as set analysis is very dynamic the evaluation of the filters is made over the entire model and direct selections may be better for the calculations. I am curious about how better it could be and would like to have more information regarding this.
