5 Replies Latest reply on Nov 8, 2017 12:09 PM by Tom W

    Multiple date formats in one chart?

    Matt Acuff

      Is it possible to force Tableau to use different date formats in the same chart?

       

      Heres what I mean:
      I have a dataset that has several thousand rows and each row has a date (1/1/15). I can organize into a bar chart and have tableau group them by year (2014, 2015, 2016, 2017). However, I would like for tableau to use a different grouping for the last bar and have it grab the last 12 months.

       

      So the resulting bar chart would look something like

       

      (2014 - Calendar year) (2015 - Calendar Year) (2016-Calendar Year) (2017-Last 12 months)

       

      Thanks

        • 1. Re: Multiple date formats in one chart?
          Tom W

          You'll need to create a calculation per year:

           

          I.e. your 2014 calculation would be;

          SUM(

          IF YEAR([Sale Date]) = 2014 THEN [Sales] END

          )

           

          2015 and 2016 calcs would be effectively the same as this, just swap out the year.

           

          Then your 2017 calculation would be more dynamic;

          SUM(

          IF [Sale Date] >= dateadd('year',-1,today()) THEN [Sales] END

          )

           

          Then use the 'Measure Values' and Measure Names to create a chart.

          • 2. Re: Multiple date formats in one chart?
            Galen Busch

            Hi Matt,

             

            There's a fairly manual way to solve this unique problem.

             

            IF year([Date]) = 2014 then date('01/01/2014')

            ELSEIF year([Date]) = 2015 then date('01/01/2015')

            ELSEIF year([Date]) = 2016 then date('01/01/2016')

            ELSEIF [Date] >= dateadd('day',TODAY(),-365) then date('01/01/2017')
            END

             

            The issue here is that 2016 will 'steal' November and December of 2016. I take it you want to double count these?

            • 3. Re: Multiple date formats in one chart?
              Galen Busch

              Yep! I like Tom's answer!


              The only issue here is that you'll lose your continuous date axis.

              • 4. Re: Multiple date formats in one chart?
                Matt Acuff

                Ok guys let me see if I got this straight.

                 

                Method 1 (Tom) is saying create a calculated field for each date? Then chart the fields?

                 

                Method 2 (Galen), your method is creating one nested IfElse statement?

                 

                Two thoughts:
                Wouldn't Method 2 result in a loop?
                Losing continuous date is a problem as I intend to have to replicate this often and present in various fashions.

                 

                Is the following possible? Let Tableau show me exclusively the last 2 months of 2016, then exclude that data and add to 2017? OR Have two data sources plotted on the same X-Axis.

                 

                IDK thanks for the help fellas.  

                • 5. Re: Multiple date formats in one chart?
                  Tom W

                  Galens IF then won't create a loop. Tableau will evaluate each row for that calculation. As soon as that calculation is true, it will return for that row. If you have a row with a date of December 25 2016, it will evaluate to 2016 in that formula. Not 2016 AND 2017, that isn't possible.

                   

                  Losing continuous date is a problem as I intend to have to replicate this often and present in various fashions.

                  I don't know if this really prevents you from replication or presenting differently. You would need to create a new calc for each year when it rolls over.

                   

                  Is the following possible? Let Tableau show me exclusively the last 2 months of 2016, then exclude that data and add to 2017? OR Have two data sources plotted on the same X-Axis.

                  You can't double count the data without creating calculations as per my previous method. So you can't represent 2 months of 2016 as 2016 and then also show it as 2017.