There are a couple of ways you might do this. The main approach, I think, is to add a parameter that allow your users to select a "custom period range" or whatever you want to call it.
Then you could either
1) Create a separate worksheet that is filtered by order date and "swap" that view into the dashboard when selected.
2) Modify your date filter calculation with an IF/THEN and filter the Order Date differently.
I'm not sure which is cleaner, and it may depend a bit on your users and use case. (1) has the advantage of a nice slider range date filter and (2) eliminated the need to show/hide a worksheet on the dashboard.
I did (1) in the attached. To get this dashboard, I
1. Added a boolean parameter "Use custom period range?"
2. Duplicated your Worksheet.
3. In the original worksheet I edited the PERIOD_REPORT date with a conditional on Use custom period range. If true, PERIOD_REPORT is null and this worksheet will disappear.
4. In the duplicate worksheet, substituted Order date for PERIOD_REPORT.
5. Duplicated category, added it to the filter shelf and added a conditional filter on this for Use custom period range. This will make this worksheet disappear when Use customer period range = False.
6. Added a horizontal container to the dashboard and put both worksheets in this container.
7. Added a field to the header that shows the date range in the view.
Again, I'm not 100% happy with this solution, but I think option (2) might require some perhaps confusing parameter naming---i.e., you don't get the nice date slider.
I tried to recreate your Option 1 in my live workbook which is using data blending between SQL Server and Tableau server data sources. I get an error msg: Tableau Data Engine Error: 4: ambiguous function call (day unknown), 2 possibile resolutions
Then I tried just doing it with one data source - SQL Server and I still get the error. Does this not work with data sources that are not Excel?
Also, I see a calculated field in your sample file - Sales Order Date PERIOD Filter. But you don't reference that in the instructions, so is that a field that you do not use?
I suspect the error is caused by the field for the date range header.
If it works when you remove this field then I'd add it again, but double check the table compute using -- you may need to use an Advanced compute using to addtrss all dimensions in the view.
Perhaps also your date field / format is causing problems.
Please let me kfind the resolution interested.
Oh, and I didn't use the Sales PERiOD field.
It does not work when I remoe the Date range header field. Did you try this using a SQL Server data source? Curious if that works for you?
Sorry jim - i think I missed the part where I need to create a condition on the Duplicated Category. How do a create a condition on a filter? I can't see how to do this.
Nevermind - just googled it. I will give that a try and see if that fixes the problem.
Could you do me a favor and test your Option 1 with a SQL Server connection and let me know if it works? I created a condition on the Duplicated Cateogry and that did not solve the problem It appears the problem is with the "Worksheet" tab (original view), as the "Worksheet Custom Date Range" works fine.
Just curious if you think you will be able to help test this in SQL Server? Otherwise, I might post as a new discussion to see if someone else can?
Sorry for the delayed response. I'm away from my computer until Thursday.
I'm happy to take a look then, but go ahead and repost if you want / need a faster reply.
Hi Jim, I can't wait. Enjoy your holiday though! Thanks!
Sorry I meant to say I CAN wait. Sorry Thanks!
Everything seems to work the same as Excel when I use either SQL Server 2012 (11.00) and SQL Server 2008 R2 (10.5).
I also noticed that I had an extra worksheet that used the continuous (green pill) PERIOD_REPORT date dimension as a filter. When switching to a custom date range, this caused an error message relating to domain padding. I've removed this sheet in the attached V2.
Also in V2, I cleaned up a few things:
- Removed unused fields PERIOD_REPORT (copy) and Sales Order Date PERIOD Filter
- On both worksheets, I edited the category filter box to reset the title (Edit title > reset) and show all values in database.
- I re-added these sheets to a new dashboard and re-organized the filters. Since Category is a global filter, only one copy of that quick filter box is needed.
- Changed the title on the order date quick filter to "Custom period range"
Hope this helps.
Thanks Jim. So it appears my issue with SQL is happening only when I am using an extract of the SQL data. When I remove the extract I no longer get the error. Does this same thing happen for you?
Interesting. I got the same error when I created an extract from SQLServer.
It's caused by the IF [Use custom period range?] THEN NULL in the PERIOD_REPORT calculated field. I was using this to make this worksheet disappear when [Use custom period range?] is true. But you can accomplish the same thing with the Category (copy) method I used in the custom period worksheet.
Here's what I did in the attached v3:
- Removed the IF THEN NULL from PERIOD_REPORT (should now look like your original calc).
- Added Category (copy) to the filter shelf in Worksheet and specified the condition for the filter as "NOT [Use custom period range?]"
- Edited Date PERIOD per table calc, since the comma was in the wrong place: May, 1 2010 instead of May 1, 2010.
Hope this works for you too. ...