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........





        • 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



                            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



                              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







                              I dont want to ignore the year btw





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

                                Actually I'm lying


                                I now see the dates as















                                Then same for Oct followed by Nov

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



                                  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