-
1. Re: Filter relative to date parameter
Joe Mako May 9, 2011 11:19 AM (in response to VusiNkomo)You can use a calculated field and a parameter control, to recreate the effect/interaction of a relative date quick filter using either a static, dynamic or user specified reference point instead of TODAY().
If you can provide some sample data, and what you expect the result to be, an example can be produced for you.
-
2. Re: Filter relative to date parameter
Alexis E May 13, 2011 2:58 AM (in response to VusiNkomo)I have that same problem.
I'd like to have two parameters X and Y, allowing me to display the last X days before date Y.
How can I do that?
-
book4.twbx 12.6 KB
-
-
3. Re: Filter relative to date parameter
Richard Leeke May 13, 2011 4:12 AM (in response to VusiNkomo)Like this...
-
book4_rl.twbx 12.6 KB
-
-
4. Re: Filter relative to date parameter
Alexis E May 13, 2011 5:04 AM (in response to VusiNkomo)Fanstastic. Thanks!
-
5. Re: Filter relative to date parameter
tobyerkson May 13, 2011 10:34 AM (in response to VusiNkomo)I agree with the OP, referencing a function or parameter would be better than a fixed date.
-
6. Re: Filter relative to date parameter
. Matthew Jun 16, 2011 4:23 AM (in response to VusiNkomo)Entry moved to Dates & Times forum...
-
7. Re: Filter relative to date parameter
Joe Mako Jun 16, 2011 4:36 AM (in response to VusiNkomo)how about something like this
-
8. Re: Filter relative to date parameter
. Matthew Jun 16, 2011 8:02 AM (in response to VusiNkomo)Joe,
This works perfectly. The alternate use of DATEADD and then DATETRUNC for this (date part) and last (date part) is nothing short of beautiful. Your solution has raised a couple of other questions though.
1. Is there a way to set the max Date 1 value as the Date 2 value and the min Date 2 value as Date 1 value? This way I can genuinely set them to Start and End dates respectively. I've looked at the 'set from parameter' but can't figure out how this works and strangely there is no mention of this control in either the manual or knowledge base that I can find.
2. Should the 'set from field' update the parameter values dynamically? It would be great to keep the borders of the custom dates within the dates held in the datasource. Ive quickly tried it with a spreadsheet and an F5 refresh but this didn't seem to work.
-
9. Re: Filter relative to date parameter
Joe Mako Jun 16, 2011 8:09 AM (in response to VusiNkomo)I am glad that helped.
as for your question 1), I wrote the calc field like:
IIF([Date 1]<=[Date] AND [Date]<=[Date 2] OR [Date 2]<=[Date] AND [Date]<=[Date 1],"Keep","Drop")
so that either date parameter could be either start or end. If you want to force one to be a start date, then you could instead have that calc portion be like:
IIF([Start Date]<=[Date] AND [Date]<=[End Date],"Keep","Drop")
If that is not what you are looking for either, that I am not sure I understand your question.
As for 2), that is not currently possible, but I agree that would be a great feature, and I recommend you speak with your account representative to request that capability.
-
10. Re: Filter relative to date parameter
. Matthew Jun 16, 2011 8:39 AM (in response to VusiNkomo)Your suggestion about editing out the 'OR' part of the calc gets me halfway there as if I set a Start Date value after the End Date it now returns nothing. However what I'd really like to do is to be able to restrict the available dates of one parameters according to the other.
So if the End Date value is set to May 24th 2011 for example, this date should now become the maximum date avaialble in the Start Date parameter. And conversely if the Start Date is set to Feb 15th 2011, this should become the minimum date available in the End Date parameter.
This way it becomes impossible for a user to pick either a Start Date after the End Date or an End before the Start Date. And what I was hoping was that the 'Set from Parameter' control might help in this.
The other thing I hoping for is that you can make some sense out of my gibberish ;-)
-
11. Re: Filter relative to date parameter
Joe Mako Jun 16, 2011 8:49 AM (in response to VusiNkomo)> to be able to restrict the available dates of one parameters according to the other
Not currently possible, another very nice idea for a feature request.
If the interaction you describe is a must have, then there is a way to accomplish it with quick filters, and setting them to relevant values only, but it requires a duplication of your data. So doable, but adds complexity, and would have to be designed specifically for your situation.
-
12. Re: Filter relative to date parameter
. Matthew Jun 16, 2011 8:54 AM (in response to VusiNkomo)OK Joe. Thanks your assistance. It's insightful as ever.
-
13. Re: Filter relative to date parameter
Richard Leeke Jun 16, 2011 8:54 AM (in response to VusiNkomo)I'm sure I have posted a "wouldn't it be nice if" posting about being able to set a pair of parameters from a single slider control. I frequently want to do that - both for dates or datetimes and also other data types. The advantage of a single slider is you wouldn't be able to slide the start date past the end date - so it is immediately intuitively clear what is happening.
-
14. Re: Filter relative to date parameter
Joe Mako Jun 16, 2011 8:58 AM (in response to VusiNkomo)I agree Richard, that would be the most ideal: a single parameter to specify a range, and with the ability to only show relative values based on the data set.