3 Replies Latest reply on Oct 12, 2016 10:04 PM by Sonali chavan

    Sorting of month

    Sonali chavan

      Hi all,

       

      I am facing a problem in sorting my month name as per the my fiscal year.

      I have to show trend on month for two years of data.

      That is why my concatenating my year and month field in one fileld, so that i will be getting continues trend for the data.

       

      what happening is, when i am concatenating month, it's getting sorted as per alphabetic order like,

       

      2015-2016APR

      2015-2016AUG

      2015-2016DEC

       

      where as i want it to be sorted as

       

      2015-2016APR

      2015-2016MAY

      2015-2016JUN

       

      which is sequence of my fiscal month.

       

      I do have a solution to this problem, that is i created on more calculated filed which gives me numbering for month.

      ex. Apr -1, May-2 etc.

       

      I am using this number field in concatenated filed, which gives me sorted order like below,

       

      2015-20161APR

      2015-2062MAY

      2015-20163JUN

       

       

      My problem is, I cant show numbers in concatenated field on my chart, and without that field i cant have sorted month names.

      I am attaching Sample sheet.

       

       

      Can anyone suggest anything about how should I hide this number from my filed or any other way to sort months????

       

       

       

       

      Regards,

      Sonali.

        • 1. Re: Sorting of month
          Simon Runc

          hi Sonali,

           

          So this is a little tricky, but think I have a solution for you (I won't go into why you are using dates in this way!....Tableau has some amazing date handling features, as long as fields are real dates, so worth taking a look...it will make your life easier!)

           

          However, onto the problem....

           

          So the first thing I did was to recreate your [Month Num] (I've called mine [Month INT])...which is likely to be more efficient than a 12 step IF statement.

           

          [Month INT]

          DATEPART('month',DATEADD('month',-3,DATEPARSE('yyyy/MMM/dd', "2015/"+[Month]+"/01")))

           

          I'll *try* and explain what it's doing! (starting in-to-out on the nesting)

           

          The DATEPARSE bit, is creating a date, for the first of the month of 2015;

          The DATEADD bit is altering the date, so that Month 1 is April

          THE DATEPART bit is then taking just the Month INTEGER out from this (so the year, I've used 2015, is irrelevant)

           

          Next bit, is to use this to create a REAL date from your YEAR and MONTH (using Month INT)

          MAKEDATE(INT(LEFT([Year],4)),[Month INT],1)

           

          Once we have this I can then use the MIN of this field to sort your String

           

           

          Hope that does the trick, and makes sense...let me know if not.

          3 of 3 people found this helpful
          • 2. Re: Sorting of month
            Dinesh Sundar Gomathinayagam

            Hi Sonali chavan

            An easier way to do this would be to avoid the concatenation. On concatenation, tableau is going to consider the field to be a string and sort it alphabetically.

             

            You can filter the data based on your date to have only two years of data and use MONTH(date) as the dimension. You can have the measures aggregated as per your need after that.

             

            Hope this helps!

            • 3. Re: Sorting of month
              Sonali chavan

              Thank you so much. The solution worked..

               

              Regards,

              Sonali