7 Replies Latest reply on Aug 26, 2016 8:11 AM by Simon Runc

    why the sorting is not correct

    Johnson Hwang

      Dears

      can someone help check why the sort is not correct? it is not sorted by descending. thanks

      2016-08-26-1.jpg

        • 1. Re: why the sorting is not correct
          Simon Runc

          hi Johnson,

           

          So the 'sort' functionality works across everything in the view...so if you removed your [Year] field, you'll see that it is sorted correctly (from a Tableau behavior point of view!).

           

          There are a few ways round this...If you want to sort the bars by the FY 2016 measure...one way is to create a calculated field which only contains the 2016 measure, so;

           

          [Sort Field]

          IIF(YEAR([Date]) = 2016, [sales], 0)

           

          btw this isn't very dynamic, as you'd need to change the formula to say 2017 next year, but we can get around that...I just want to show you the general principle here.

           

          If you then set this new field to be your sort (as a SUM) that should do the trick.

           

          Hope that makes sense, and works...let me know if not

          • 2. Re: why the sorting is not correct
            Johnson Hwang

            Thanks Simon,

             

            the sort field don't work here since my "Date" field need chagne to Fiscal year which acorss 2015 and 2016,

             

            e.g. 12/2015 is Fiscal FY16

             

            do we have any other dynamic way,

            • 3. Re: why the sorting is not correct
              Carl Slifer

              Hi Johnson,

               

              In your dimensions pane right click your date field that you've already changed the fiscal year on.

              Go to create -> Custom Date

              Then choose Years and select DatePart and Year

               

              Then use this field in your calc field.

               

              IF [DateField (Year)]  = 2016 THEN Sales ELSE 0 END

               

              And use this as your object to sort on.

               

              Best Regards,

              Carl Slifer

              InterWorks

              1 of 1 people found this helpful
              • 4. Re: why the sorting is not correct
                Simon Runc

                OK....well we're going to have to get a little creative!

                 

                So I can force the YEAR (DATEPART) function to act like FY, by fooling Tableau!, using the DATEADD...

                 

                I create a field which returns the YEAR in fiscal year, with the following (you can change the -3 for your starting FY month -1);

                [FY Year (starting April)]

                DATEPART('year',DATEADD('month',-3,[Order Date]))

                 

                and then I can proceed, as before with this field.

                [Sort on FY 2012/13]

                IIF([FY Year (starting April)] = 2012, [Sales],0)

                 

                and use this on the sort. The 'How it works' tab, shows how the [FY Year (starting April)] works.

                 

                Let me know if that does the trick!

                • 5. Re: why the sorting is not correct
                  Simon Runc

                  Thanks Carl...I always forget about that option!

                   

                  Yes Carl's method is much better (...if not as fun!). I've updated the attachment to show both (but I'd go with Carl's)

                   

                   

                  ...Just out of curiosity I wanted to see what the custom date (generated) was doing...

                   

                   

                  It's fooling itself with the same trick!!!

                  • 6. Re: why the sorting is not correct
                    Johnson Hwang

                    thanks Carl and Simon,

                     

                    it does works now, but I'm still looking forward a daynamic way instead of new a individual field to sort.

                    • 7. Re: why the sorting is not correct
                      Simon Runc

                      So in terms of dynamic...I assume you mean that you don't want to hard-code 2016 into the formula?

                       

                      We can fix this by using a FIXED LoD

                       

                      so in Carl's version, if you switch out the 2016 with the following {MAX([DateField (Year)])} this will be the last year of the data (as defined by FY...also {MAX([DateField (Year)])}-1 would be the year before and so on)

                       

                      So would be

                       

                      IF [DateField (Year)]  =  {MAX([DateField (Year)])} THEN Sales ELSE 0 END

                       

                      and so this calc updates to sort by last year, whatever the data.