1 of 1 people found this helpful
Hi Mehmet -
There is only one way to deal with date-related filters on the URL or with TabCmd, and that is by passing a specific date in the format of:
&LogDate=2013-01-23%2015:05:02 // 3:05:02p 23-Jan, 2013 (passing the time is optional)
In your shoes, I would create a copy of the current report without the date filter in place, and I'd create a new filter/expression which is driven by a parameter (which you can set on the URL or via TabCmd).
The expression would simply determine whether rows have an associated LogDate within the LastN months. You could use many combinations of DateDiff(), DateAdd(), etc expressions to do this, and your calculated field would simply return T/F - You filter for "T" - give me only rows over the LastN months.
You'd pass in the LastN value as a parameter:
//off the top of my head, probably wrong, but if you know about Fiddler, then this part is trivial to you!
[LogDate] >= DateDiff("month", (-1 * [Parameters].[LastN]), Now())
Thanks for that Russell, I'll give your suggestion a try as well.
As a workaround I was deploying the report twice, one under the main project bucket and I had another bucket for publishing reports which essentially had no filters or had 'Previous Month' set so that it would be distributed on the first day of the month for data of the previous month.
The only concern I had with deploying in the above scenario was the data-connection having the same name twice plus refreshing twice.
I realize its been a while since you posted this, but I have struggled with the same issue for the last three days and finally came up with my own solution that I wanted to post here as an alternative option to yourself and other users.
- - Tableau requires dates to be passed in the following format: “yyyy-mm-dd”.
- - Because we are working with date ranges, we have the issue of getting all the dates in the range to export.
- - There is no >= or <= operators available when passing filter values via a URL
How I got around this:
My solution was to pass filter values to the “Date Month” field in the URL. This way, all dates that fall within a date month for which we are filtering will be exported. My main problem was that the user is not selecting individual months, instead the user is selecting a range of dates defined by a parameter. So the user selects values like “Last 3 months”, “Last month”, etc. So, in my end solution, my url string looks something like this:
which when I click on it resolves to something like this:
My solution is as follows:
- - Create a new calculated field (lets call this Calc1) that derives the date values to select based off of the time frame selected by the user
- - Create another calculated field (lets call this Calc2) that is a varchar field that constructs the month/year of Calc1 in the format of yyyy-mm-dd, where “dd” is always “01”. The key here is this MUST resolve to a string so the default Tableau date formatting doesn’t override our format when we use this as a filter.
- - Drag Calc2 onto the filter pane and select “Show filter”. Now you will see all of the date month strings that are available in the database. Make sure this filter values are showing as a multi-select list.
- - Here is the KEY step that makes this work: Make sure you click on the filter control for Calc2 and select “Only Relevant Values”. This will ensure that only the months that correspond to the date range the user has selected appear in the filter drop down.
- - Place the new filter on the dashboard where the URL action will be located. If you don’t want to show this filter for aesthetic purposes, you can add a floating container on top of it to hide it from the users view.
- - Publish and test your workbook! This should work like a charm.
NOTES: For a while, I was using the “Test Link” option in the Dashboard Actions UI within Desktop to test out my solution. Plese note that this link will ONLY ever show you the first three values of the data you are selecting. I didn’t see any place where this was documented and I wasted a whole lot of time thinking my solution wasn’t working before I figured this out.
I hope this helps anyone who has struggled with allowing a user to export data based on a date range.