4 Replies Latest reply on Sep 29, 2017 8:00 AM by Sindhu Giridharan

    Rolling Date Range in String Format

    Sindhu Giridharan

      Hi,

       

      We are working with data which is a 12-month time stamp i.e., every quarter, the data is refreshed to add a new quarter and drop the first quarter. For example: Apr15 - Mar16, Jul15 - Jun16, Oct15 - Sep16 and so on.

       

      We'd like to create graphs that calculate the top 5 revenue types for the latest timeframe and also compare the values for those revenue types during other timeframes.

       

      We are working off of an excel data source which I have mocked up and attached.

      Any help will be appreciated!

       

      Thanks,

      Sindhu.

        • 1. Re: Rolling Date Range in String Format
          Shawn Wallwork

          Sindhu, happiness in just a few steps:

           

               1. Go to the Edit Data Source and 'Split' Time Frame field

               2. Go to worksheet and change the default field type to Date for the two new split fields.

               3. Build charts/dashboards!

           

          Note: Whenever possible always work with Date type date fields; meaning use whatever technique you need to convert strings to dates. This makes for much happier chart building.

           

          If you would have mentioned a Tableau version number, I would have posted a workbook for you. But here's a screenshot:

          --Shawn

          2 of 2 people found this helpful
          • 2. Re: Rolling Date Range in String Format
            Simon Runc

            hi Sindhu,

             

            Shawn beat me to it!...but 2nd his advice to work with dates.

             

            I took a slightly different approach using this calculation to drag out the date (I'd go with Shawn's...a little more elegant!)

             

            [Time Frame Start Date]

            DATE(DATEPARSE('ddMMMyy', '01'+ LEFT([Time Frame],5)))

             

            In addition I also added this Quarter Index calculation. By using this the last quarter will always be 0, the one before -1, then -2...etc. This is a really good way to cut down on the maintenance of your models when a new quarters data comes in

             

            [Quarter Index]

            DATEDIFF('quarter',[Time Frame Start Date], {MAX([Time Frame Start Date])})*-1

             

            Hope that helps.

            1 of 1 people found this helpful
            • 3. Re: Rolling Date Range in String Format
              Sindhu Giridharan

              Thanks Simon.

              I will blend both your suggestions.

              The Indexing is where I am running out of ideas. This should help me!

              • 4. Re: Rolling Date Range in String Format
                Sindhu Giridharan

                Thank you Shawn. I was splitting it earlier on Excel, but your method is a more viable option in the long term.

                In addition to this, I will add Simon's Indexing method to get the top 5 ranks. Thanks again!