12 Replies Latest reply on Dec 19, 2016 1:21 AM by Simon Runc

    Sort the result

    Sultan Altowerqi

      Hi folks,

       

       

      I hope all are OK.

       

       

      I am new with Tableau and I have build the attach_1 work sheet which is describe the total profit and total lose by country my question is how can I sort it depends on the positive and negative signs. Moreover I found a way to select the positive and sort it DESC or ASC and do the negative as well in a same way see attach_2 but this doesn't working fine because I have use the date filter so if I change the date the positive and negative will be order in a differently way see the attach_3.

       

       

      Is there a way to sort it such as the attach_1 and doesn't effect if I a change the date?

       

       

      One more thing, is there a way to put the Dimeension between the negative and positive signs instead of the left poisition?

       

       

      I appreciate your assistance in this matter.

       

       

      Thanks in advance,

      Sultan

        • 1. Re: Sort the result
          Simon Runc

          hi Sultan,

           

          So one way to achieve this is to use a Blue pill to "force" a sort in Tableau. By default Tableau sorts Blue pills in Ascending order (be that by value or alphabetically), which means the negative of the value is a descending sort!!

           

          In the attached I've mocked up the YoY (for last year) in Super Store by Category...which is done via the [Sales Var YoY] measure

           

          (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

           

          I've then created the following formula off this (just the negative of the YoY formula)...

          [Sales Var YoY - Sorter]

          [Sales Var YoY]*-1

           

          I then change this to be discrete (Blue) and bring it into the left of the thing I want to sort (SubCategory in this case). I then hide the header...as you'll see you can filter on the Months and it always re-sorts in descending order.

           

          hope that helps and makes sense, but let me know if not

           

          ...not sure I understand what you mean in your second question...can you show me an example of what you'd like to see.

          1 of 1 people found this helpful
          • 2. Re: Sort the result
            Sultan Altowerqi

            Thanks Simon I really appreciate it.

             

            Ignore the second question I have a new one .

             

            How can I sum the profit from the beginning of the seasons until specific date?

            • 3. Re: Sort the result
              Simon Runc

              cool...glad it did the trick.

               

              Well it depends on how you want to show it...

               

              do you want a running sum by month (bottom chart)?

               

               

              or do you just need a field whose SUM is the profit only to a specified date?

               

              and on either option, how do you want to define the "Specific Date"? via a calculation, or a user filter/parameter?

               

              If you let me know on the above I can let you know a way to achieve it.

              • 4. Re: Sort the result
                Sultan Altowerqi

                Thank you so much.

                 

                Sorry for asking so much questions. One more, How can I take the Top 5 + and Top 5 - in the same sheet.

                • 5. Re: Sort the result
                  Simon Runc

                  hi Sultan,

                   

                  So I assume you mean top 5 and bottom 5?...

                   

                  This thread gives several ways to do it, with good explanations (...personally I like the sets method)

                   

                  Select and view Top N and Bottom N on Same Worksheet

                  • 6. Re: Sort the result
                    Sultan Altowerqi

                    Hi Simon,

                     

                    Thanks for getting back to me.

                     

                    This is not what i want. I want to display the top 5 positive and the the 5 minus in the same time like the attachment.

                     

                    Thanks in advance,

                    Sultan

                     

                     

                    Attach_9.JPG
                    Attach_10.JPG

                    • 7. Re: Sort the result
                      Simon Runc

                      So the same basic method works here...

                       

                      First I create a RANK on YoY, which is the equivalent of the index() in Jim's post

                      [Rank on YoY]

                      RANK([Sales Var YoY])

                       

                       

                      I can then use his exact formula (substituting the index() for RANK)

                      [Show Top 5 and Bottom 5]

                      [Rank on YoY] <= 5      

                      OR (SIZE() - [Rank on YoY]) < 5

                       

                      and set up the compute using on the table calcs as follows (the red box indicates where you can set up the different nested calcs differently)

                       

                      I've left the calculations in the view, so you can see what's going on...but you can just drag the [Show Top 5 and Bottom 5] field to the filter and set to true to only show the Top and bottom 5

                      • 8. Re: Sort the result
                        Sultan Altowerqi

                        Simon you are the best thank you so much

                        • 9. Re: Sort the result
                          Sultan Altowerqi

                          Hi Simon,

                           

                          Last questio I pro you. How can I put the positive result and negative one in separate sheet? " the positive in sheet and negative in different sheet.

                           

                          Thanks in advance,

                          Sultan

                          • 10. Re: Sort the result
                            Simon Runc

                            hi Sultan,

                             

                            So to do this we can create the following calculation (on our YoY field)

                             

                            [YoY Positive/Negative Filter]

                            [Sales Var YoY]>=0

                             

                            we can add this to the filter shelf...and set to true (ensuring the compute using is Order Year)...this is the positives

                             

                            then duplicate the sheet, and set the [YoY Positive/Negative Filter] to false (in the filter)...this is the negatives.

                            • 11. Re: Sort the result
                              Sultan Altowerqi

                              Hi Simon,

                              This is not working fine on my sheet. Let me explain what I did.

                              1. I have two season data so I created a calculated field:   IIF(([UR_SEASON]) = 2016, [Sales],0) I called this 2016 Sales

                              2. Another cal filed another season IIF(([UR_SEASON]) = 2015, [Sales],0) I called this one 2015 Sales

                              3. Difference Calculated filed [2016 Sales]-[2015 Sales]      I called this field Diff

                               

                              When I try to use your solution [Diff]>=0 it gave me something wrong for example Pakistan country it will appear in the positive and in the negative sheet. 

                              Simon Runc

                              • 12. Re: Sort the result
                                Simon Runc

                                I think this is due to the Diff being carried out at row level (so not an aggregated calculation).

                                 

                                When you create a field such as IIF(([UR_SEASON]) = 2016, [Sales],0)...when you bring it into the canvas it is brought in as a SUM, and so the Diff also needs to be wrapped in a SUM aggregation. Below is a table showing what happens at Row Level

                                 

                                 

                                So if you make your Diff calculation SUM([2016 Sales])-SUM([2015 Sales]), they your >0 part will work over the aggregate, and should do what you want.