Relative date filter is nothing more than a query that has a WHERE clause that looks something like this:
WHERE [Date] > TODAY() AND [Date] < DATEADD("month", 12, TODAY())
Whatever is captured by that query is what will get displayed and added up in Grand Total. There's no "first" and "subsequent" runs - every time it'll run it'll get reevaluated.
As for anchor - instead of TODAY() you'll see that anchor date substituted and that's it.
To clarify, answer this:
- what date range do you want to see when this viz is rendered on 7/1/2016?
- what date range do you want to see when this viz is rendered on 7/31/2016?
- what date range do you want to see when this viz is rendered on 8/1/2016?
- what date range do you want to see when this viz is rendered on 1/1/2017?
- what date range do you want to see when this viz is rendered on 7/1/2017?
Thanks for the reply.
In order to capture the entire (previous) month I would probably run this on the 1st of the following month. So, running this for the first time on 8/1/2016 I would want to see July 2016; for 9/12016 I would want to see August 2016 and July 2016; for 10/1/2016 I would want to see Sept 2016, August 2016, July 2016; when it gets to 7/1/2017 I would want to see June 2017 thru July 2016, same range as depicted in the example except increment the year by 1; on 8/1/2017 I would want to see July 2017 thru June 2016, etc. Basically this viz needs to process and display the previous month for 12 months in descending order and at the end of the 12 months, the oldest month needs to drop off as additional months are processed and added, hence the rolling months. Based on your reply it sounds like I need something in the way of a calculation in order to achieve this. Do you or anyone reading this have any suggestions? Thanks again,
OK, I finally had time to look at this.
TO achieve what you want you need 3 filters for your date column
- Date range filter that filters out everything before July 2016
- Relative date filter (on a copy of date column) to show "last 13 months"
- Calculation borrowed from here to exclude current month
See attached workbook.
community.twbx 48.4 KB
1 of 1 people found this helpful
Thanks for the reply. After reading through your response and looking at the twbx file, I reread my last post and I think I need to add further clarification. I'm going to step through with screen shots what the viz should look like after each monthly run for the first 12 months plus 2 additional months after the months roll to give a better visual of what I'm trying to accomplish. Please refer to the attached word doc. Is there a way to dynamically create something comparable to the attached in Tableau? Sorry for any lack of clarity and Thanks again for your assistance.
I spent some more time looking at your proposed solution and this is something that I can work with moving forward. Thanks for all of your help.