5 Replies Latest reply on Aug 9, 2016 10:02 AM by mike sutton

    Relative dates as rolling months

    mike sutton

      Hello,

      I'm trying to create a Tableau Worksheet that displays a dept., test code, grand total count for the codes, and a monthly count for the codes in a rolling month display whereby the month/year is in descending order (see 1st image below).  I've been testing this using a relative date filter on the data source (see 2nd image below) but I'm not completely sure how this would work when running unattended on a schedule in production on a monthly basis.  When this runs for this first time, e.g. 7/31/2016, I want it to populate that month and add to the grand total; when it runs for subsequent months, I want it to similarly populate that particular month and add to the grand total, etc.  What I'm uncertain about is using Today as the anchor.  If I create this on 7/31/2016, will Tableau "remember" that date moving forward thereby creating data columns for months July 2016 thru June 2017?  If that's the case, with the range set for the next 12 months, when it gets to July 2017, will the months roll so that July 20016 drops off the display to be replaced by July 2017 and continue on in that fashion (oldest month drops off the end of the display as the new month is added to the beginning of the display)?  What is the best way to create a 12 month rolling date range like this in Tableau?  Thanks in advance.

       

      Mike

       

      Relative Dates as Rolling Months.jpg

       

      Relative Dates as Rolling Months_2.jpg

        • 1. Re: Relative dates as rolling months
          Dmitry Chirkov

          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?

          • 2. Re: Relative dates as rolling months
            mike sutton

            Dmitry,

            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,

             

            Mike

            • 3. Re: Relative dates as rolling months
              Dmitry Chirkov

              OK, I finally had time to look at this.

               

              TO achieve what you want you need 3 filters for your date column

              1. Date range filter that filters out everything before July 2016
              2. Relative date filter (on a copy of date column) to show "last 13 months"
              3. Calculation borrowed from here to exclude current month

               

              See attached workbook.

              • 4. Re: Relative dates as rolling months
                mike sutton

                Dmitry,

                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.

                 

                Mike

                1 of 1 people found this helpful
                • 5. Re: Relative dates as rolling months
                  mike sutton

                  Dmitry,

                  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.

                   

                  Mike