1 Reply Latest reply on Dec 7, 2015 2:49 PM by Simon Runc

    Sort month in the order of the TTM, without Year

    nanlu.shan

      I have a data source which has data organized like this:

      YearMonthRevenue
      20151$100
      201412$200
      201310$300
      201411$400

       

      I want to do a simple trailing 12 month (TTM) line chart of the revenue for current period (TTM CY) vs prior period (TTM PY), for example, [Dec 2014 - Nov 2015] vs [Dec 2013 - Nov 2014]

       

      I have calculated fields created to indicate if the entry is TTM CY or TTM PY based on current date, and was able to create the chart.  But then I realized the Month defaults to the order 1,2,3,4....12 in the X axis label.  I'd like it to show December first, as it is the first month in the TTM period, so it should show 12,1,2,3,4....11

       

      I can of course manually change the order but come next month I'll have to do that again manually, not ideal.  Or, I can rearrange and aggregate my data source into Current Period and Prior Period columns before feeding it to Tableau, but this option is out of the question as the data source is massive and required too much of a change to the current workbook structure.

       

      So is there a workaround to make the month appear in the order of the TTM dynamically? I'm on Tableau 9.0.2.

       

      Hope this makes sense and thank you in advance!

        • 1. Re: Sort month in the order of the TTM, without Year
          Simon Runc

          hi Nanlu,

           

          So I think he problem you are having here, is that you are using the date as a DATEPART and not a DATETRUNC (in the date options the top set are DATEPART, so months, and the second set are DATETRUNC so month year). I'm not sure how you've calculated your rolling, so I've done this here and used as a filter. I've also filtered the data, as the superstore data set I have goes to 31/12/13...so wouldn't demonstrate the point very well, being the only month where your current version works!

           

          Using the Date at Month level they are now plotted in the correct order. If you want the axis to just have the month, you can set this as a display as in the formatting for the date.

           

          Hope this is what you wanted...and makes sense, please post back if not