1 2 Previous Next 18 Replies Latest reply on Jun 19, 2018 9:23 PM by Peter Fakan

    Interactive sort on Dimensions in Row

    Graham A

      Hi All,

       

      I am new to all this so please excuse me if this is a stupid question.

       

      I have been using Tableau desktop for around two weeks now, pretty impressed to be honest. But my first report delivery is for our Sales and Marketing teams. The report I have created uses only Dimensions placed on the Row shelf to show information from our db on sales Leads.

       

      The report has 13 of them for things like Last Name, First Name, Email addr, Date created, Date Modified etc and additional filters to narrow down the list.

       

      But what I need to do is post this to Tableau Server and allow the users (interactors) to sort on any of the Rows and I can't seem to figure out/if this can be done and how........

       

      Thanks

       

      G

        • 1. Re: Interactive sort on Dimensions in Row
          Joe Mako

          Take a look at the attached, I used a parameter and a calculated field to allow the user to change the sort order of the first dimension.

           

          Would you like any details on any of the techniques used in this workbook?

          • 2. Re: Interactive sort on Dimensions in Row
            Graham A

            Thanks Joe.

             

            Almost there apart from getting the Parameter to show as a Quick Filter, think I have everything else working

            • 3. Re: Interactive sort on Dimensions in Row
              Graham A

              ahh....got it!

               

              Show Parameter control

              • 4. Re: Interactive sort on Dimensions in Row
                Graham A

                Brilliant.....Thanks Joe all working

                 

                One last question on this

                 

                Certain Columns I would want in Ascending order (Last Name)and others in Decending order (Date=Newest) can this be done?

                 

                It seems so, I dont know what I have done but Name is sorting A-Z and rating is sorting Z-A??????

                • 5. Re: Interactive sort on Dimensions in Row
                  Joe Mako

                  I don't know of an easy way to control that without preprocessing.

                   

                  Thinking about, likely over-thinking, maybe an interesting/complex set of parameters and table calculations could allow you that control.

                   

                  Anyone know if there is an easier way?

                  • 6. Re: Interactive sort on Dimensions in Row
                    Richard Leeke

                    My first thought (which I'm sure Joe had already discarded) was that a table calculation which just swaps the order of rows on the fields you want as descending sorts would do it.  But you can't sort on the results of a table calculation (which makes sense - everything would get very circular).  You could still do something by defining calculated fields to reverse the order of all fields, but that would be just horrible.

                     

                    The best I can see is that for any fields that you want to sort descending you'd need to create an expression which returns something which sorts in the other order and use that instead of just the original field name in the CASE statement in the [Sort] calculated field.  That's slightly tricky because it needs to be a string expression, so numeric fields would need to be padded with zeroes, for example.  (A FORMAT() function would be really handy here.)  So for your date field you want to create a calculated field saying how many days ago the date was which will give you newest first.  So:

                     

                    NOW() - [DATE]

                     

                    Would give you the number of days since the date, which will sort the way you want numerically.  But to turn that into a string so that "2" comes before "11" you need to pad it.  If you need to cope with future dates you also have to cope with that expression going negative, so the easiest would be to add a huge offset to the number.  This is good for the next few hundred years:

                     

                    RIGHT("000000" +STR(TODAY()-[Date]+100000), 6)

                     

                    So assuming there was an additional [Date] field in the example Joe posted, the [Sort] calculated field would become:

                     

                    CASE [Sort by]

                    WHEN "Customer Name" THEN [Customer Name]

                    WHEN "Region" THEN [Region]

                    WHEN "Zip Code" THEN STR([Zip Code])

                    WHEN "Date" THEN RIGHT("000000" +STR(TODAY()-[Date]+100000), 6)

                    ELSE NULL END

                    • 7. Re: Interactive sort on Dimensions in Row
                      Joe Mako

                      Great idea Richard, I was over thinking it :)

                       

                      but why not just:

                       

                       

                      WHEN "Date" THEN -FLOAT([Date])


                       

                      for the line in question.

                       

                      EDIT: Does not work because it is not text -I'll have to come back to this in a bit

                       

                       

                      The issue I see with using TODAY/NOW functions is what if the date is in the future?

                      • 8. Re: Interactive sort on Dimensions in Row
                        Richard Leeke

                        The future date issue is why I added 100000 to the number of days (which is 274 years, so I won't be around to care when that stops working).  That just avoids the problem with sorting on text representations of negative numbers.  A bit grubby, but it works and I really didn't think it was worth sweating it much more than that.

                        • 9. Re: Interactive sort on Dimensions in Row
                          Graham A

                          Thanks Richard,

                           

                          It almost works :)

                           

                          I now see the dates in the order of:

                          November 9

                          November 8

                          November 7

                          November 25

                          November 24

                          November 23

                          ....

                          November 10

                          • 10. Re: Interactive sort on Dimensions in Row
                            Joe Mako

                            Graham,

                             

                            would those full dates be:

                             

                            November 9, 2010

                            November 8, 2010

                            November 7, 2010

                            November 25, 2009

                            November 24, 2009

                            November 23, 2009

                            ....

                            November 10, 2009

                             

                            Attached is a workbook showing this sorting like this.

                             

                            Would you rather it ignore the year, and just sort by the day of year?  (also included in attached)

                            • 11. Re: Interactive sort on Dimensions in Row
                              Graham A

                              Joe,

                               

                              Sorry all the dates are 2010

                               

                              I must be missing something because I don't get the same reults as your example.

                               

                              I have:

                              1. Amended the CASE statement

                              2. Set the Date dimension to "All values" and "Discrete"

                              3. If I look at the Calculated field dialogue select the date field in question and "Describe" then "Load" I see a list of dates in dd/mm/yyyy format

                               

                              Yet I still see the dates sorting as

                              02/09/2010

                              03/09/2010

                              29/03/2010

                              28/09/2010

                               

                               

                              I dont want to ignore the year btw

                               

                              Thanks

                               

                              G

                              • 12. Re: Interactive sort on Dimensions in Row
                                Graham A

                                Actually I'm lying

                                 

                                I now see the dates as

                                06/09/2010

                                05/09/2010

                                04/09/2010

                                30/09/2010

                                03/09/2010

                                29/09/2010

                                28/09/2010

                                ...

                                02/09/2010

                                19/09/2010

                                18/09/2010

                                ...

                                01/09/2010

                                 

                                Then same for Oct followed by Nov

                                • 13. Re: Interactive sort on Dimensions in Row
                                  Joe Mako

                                  Graham,

                                   

                                  I am unable to find a way to get the sort order your are getting.

                                   

                                  Can you provide a packaged workbook showing that sorting situation?

                                  • 14. Re: Interactive sort on Dimensions in Row
                                    Richard Leeke

                                    I haven't looked at the workbook or read this carefully - but it sounds a bit like a nasty issue you can get with text data sources (which use the MS Jet engine) - which can mangle dates.  I think this is locale dependent.  It certainly happens to me in NZ where we don't use US date order.

                                     

                                    What can happen is that day and month get transposed but only for the dates where the day <= 12.  So 23/11/2010 is unambiguously 23rd of November, whereas 03/11/2010 may get treated as 11th March.

                                     

                                    So if your data source is a text file and the original dates are in dd/mm/yyyy order I'd have a careful look at those dates - look at the details of a few rows in the text file and in Tableau and see if the dates are being handled right.

                                     

                                    If it is that your options are to reformat your text file, define a schema.ini file as described in a knowledgebase article or do some ugly calculated fields in Tableau to unpick the mess.

                                     

                                    Edit: I think that issue I was talking about may happen when you have a text file that doesn't match the default date order for your locale.  Maybe.  I know we only see it from time to time.  I had it this week and that was certainly a file in MM/DD/YY format, which doesn't match the default for us.

                                    1 2 Previous Next