7 Replies Latest reply on Jun 28, 2016 10:14 PM by Gopi Chand

    Custom sorting on Lookup table

    Gopi Chand

      Hi All,

       

      I have a requirement where I need to sort my table (Difference) with selected measure. I able to do sorting in all other tables by using 'Sort by' calc field but except this one (attached is a sample of my data). I have gone through below threads and got much information however, I'm not able to sort this piece.

       

      Multiple dynamic sorting, indexing and lookup ; Sorting by difference ; by Jonathan Drummey

       

      Note: I dont want to sort by the difference but selected measure which is there in 'Sorting' parameter.

       

      Attached is a sample workbook.

        • 1. Re: Custom sorting on Lookup table
          Joe Oppelt

          (Note to self:  9.0 workbook)

           

          Gopi -- I have this open, but I'm not sure of what you want to achieve.  Are you looking to sort Sub-Category by [Sales] in the FAILED sheet?

          • 2. Re: Custom sorting on Lookup table
            Joe Oppelt

            Actually, your FAILED sheet is sorting by the sum([Sorting Field]) correctly.

             

            In the attached I have added SUM(Sorting Field) and a total of that SUM for each Sub Category.

             

            When you have multiple months, tableau grabs the sum of the sum of [Sorting Field] to compute the sort.  (That's what the WINDOW_SUM is simulating.)  If you just want it to obey the number in one of those months, you'll have to decide which month you want it to obey and tell Tableau only to consider the values for just that one month.


            We can discuss how to make that happen if that's what you really want to do here.

            1 of 1 people found this helpful
            • 3. Re: Custom sorting on Lookup table
              Gopi Chand

              Hi Joe Oppelt,

               

              Thanks for your prompt response and Yes you understood my requirement perfectly.

               

              Your window_sum formula is working as expected (Many thanks for this). I have modified sorting field formula as below to connect with date parameter (Base Month) and it is  working very fine.

               

              IF [Base Period]=[Base Month] THEN

              CASE [Sorting (copy)]

              WHEN 'Sales' THEN [Sales]

              WHEN 'Profit' THEN [Profit]

              ELSE [Quantity]

              END

              END

              However, I wanted to achieve below requirement.

               

              I would like to use a filter called "Current Month" and view should sort by current month value (I'm aware that current filter is  not involving in this view but still that is acting in other dashboards so it has to act here as well). I'm attaching the workbook where I have included current month parameter in the view.

               

              Excuse me if I'm giving trouble to you and appreciate your help on this.

               

              Regards,

              Gopi

              • 4. Re: Custom sorting on Lookup table
                Joe Oppelt

                From what I am seeing, it looks like the [Current Month] parameter and the [Base Month] parameter are identical.  And the way your calc described above is using [Base Month] is a good way to do what you need to do.

                 

                Is [Current Month] expected to do something in addition to [Base Month]?  I'm just not understanding why you need both of these.

                • 5. Re: Custom sorting on Lookup table
                  Gopi Chand

                  Hi Joe Oppelt,

                  Good Morning!!

                   

                  As I mentioned earlier [Current Month] is using for sort in other places. In my workbook I have 4 dashboards. In that 3 dashboards are sorting by [Current Month] and truly we need sorting by [Current Month] in all the 4 dashboards.

                   

                  Now as per your window_sum calculation I able to do sort for this sheet (FAILED) by using only [Base Month] but I need sorting by [Current Month] hence all the dashboards will be at same page (sorted by [Current Month]).

                   

                  Hope this can help.

                   

                  Regards,

                  Gopi

                  • 6. Re: Custom sorting on Lookup table
                    Joe Oppelt

                    Then all you need to do is change the calc you listed above.

                     

                    When you did this:

                     

                    IF [Base Period]=[Base Month] THEN

                    CASE [Sorting (copy)]

                    WHEN 'Sales' THEN [Sales]

                    WHEN 'Profit' THEN [Profit]

                    ELSE [Quantity]

                    END

                    END

                     

                    You loaded the appropriate value in the calc only for the month being targeted.  (  [Base Period]=[Base Month]  ).  All other months have null.  So when Tableau does SUM([Sorting]) it only has data in the base month and you get sorted only by the values in that month.  So if you want to sort by [Current Month] instead of [Base], just insert [Current Month] in there instead.

                    • 7. Re: Custom sorting on Lookup table
                      Gopi Chand

                      Yes,

                       

                      It's working Joe Oppelt!!!

                       

                      Thanks alot for your valuable time on this.

                       

                       

                      Regards,

                      Gopi