1 2 Previous Next 18 Replies Latest reply on Apr 7, 2017 7:47 AM by laura.winger.0

    Sorting N/A's

    Orlando Suarez

      Quick question here regarding N/A's


      In Tableau 10 you can right click on a measure and you get an option on the panel to the left that allows you to add N/A to any NULL or blank values. In my case I have a Month over Month Measure where some items had no values. This feature worked well because it filled in all of my blanks with N/A's. However that M/M column/ measure is tied to a sorting parameter. When I hit the parameter it sorts the values in descending order. However with the N/A's now in the mix it places all of the N/A's at the top of the sort.....when I really want all N/A's at the bottom of the sort. (see attached screen grab)


      How do I get the N/A's down to the bottom of the sort?  Can anyone give me some workarounds or ideas?

      Sorting Not Availables.png

        • 1. Re: Sorting N/A's
          Walt Reed

          Hey Orlando,

          I'm not sure whether the Null values you want to keep as N/A or not. But if not, you could use the ZN and LOOKUP functions (e.g. ZN(LOOKUP(sum(M/M))). This will replace null values with a 0, then you'll be able to sort accordingly.



          • 2. Re: Sorting N/A's
            Orlando Suarez

            Thanks for the suggestion Walt. My company actually prefers the N/A over 0's. So hoping to get a solution for sorting with that.

            • 3. Re: Sorting N/A's
              Jeremy Harris

              If you create a calculated field that uses ZN as Walt suggested, added it to the detail shelf, then use the "Sort by Field" option on that calculated field, would that do what you're looking for?

              • 4. Re: Sorting N/A's
                Walt Reed

                Here's another thought (courtesy of Sort NULL values )


                Step 1. Create a field (First calculated field) along these lines :

                IF IFNULL([M/M],0)=0 THEN 'N/A' ELSE STR([M/M]) END


                Step 2. Create a string parameter ([NA Parameter]) and set the current value to "N/A"


                Step 3. Create another field (Second calculated field) along these lines:

                CASE [NA Parameter]

                     WHEN 'N/A' THEN [First calculated field]



                Step 4. Place the [Second calculated field] onto the shelf (you might have to play around with where it's placed, but possibly after Segment), then manually sort and place the N/A at the bottom. Then hide the header so the calculated field isn't visible.



                • 5. Re: Sorting N/A's
                  Orlando Suarez

                  Thank Walt and Jeremy for your continue assistance...really appreciated.


                  Here's the thing. I've already got an overall parameter in place that sorts columns by user selection. This overall parameter is called my 'Sort by' Parameter. On my dashboard a user comes here to sort by Share of Seg, M/M, Y/Y , Segment Rank or Model. The calculation ('Sort% and Rank' on the Rows shelf as a discrete pill) that powers the parameter is written to already sort in descending order. So creating a separate parameter just for the M/M column I don't think would be feasible because it is not tied to this overall parameter I already have....plus it would entail a second step in the process for the user???


                  So I'm thinking that the issue has to be addressed either at the M/M % Change (measure) calculation......or at the Sort% and Rank calculation.....perhaps then some instance of your "WHEN 'N/A' THEN [First calculated field]" is then placed inside of one of these ?


                  I just don't know how to write that or if it is even possible.


                  By the way, whatever is made for the M/M also has to be applied to Y/Y.


                  I've attached a broader screen grab with visuals into how each calculation and parameter is already set up....hope this helps.

                  NA Sort Issue.png

                  • 6. Re: Sorting N/A's
                    Walt Reed

                    Hey Orlando,

                    That definitely throws a wrench into this. I like the way that you set up the parameter to do the sorting--I've never seen that before.


                    Is there any way you could attach a copy of your workbook so I could play around with it? I had created a dummy file to try and find a solution, but with you having the parameter and custom calculations, this route would be easier.



                    • 7. Re: Sorting N/A's
                      Jian Wang

                      I don't have a computer beside me to try. Have you tried to use -999(N/A) vs N/A for the Null value.

                      • 8. Re: Sorting N/A's
                        Orlando Suarez

                        Hey Walt. I wish I could load a copy but it's protected company info. It's a monster of a book also. I can provide screen shots and cover sensitive areas.

                        • 9. Re: Sorting N/A's
                          Walt Reed


                          That's a really good thought. I played around with that yesterday and it seemed to work better. Only problem I ran up against was the values sorted from smallest to largest, and I couldn't figure out a way to sort from largest to smallest without manually sorting them.


                          • 10. Re: Sorting N/A's
                            Orlando Suarez

                            Well If I place -999 as a replacement to N/A then the -999 shows in the column. I know my people will question that. Is there a way to do an alias on the values so even though Tableau sees it as -999 for sorting purposes, users see an alias of N/A?

                            • 11. Re: Sorting N/A's
                              Walt Reed

                              You can hide the column by clicking on the pill and unselecting "Show Header"

                              • 12. Re: Sorting N/A's
                                Orlando Suarez

                                But if I hide the column then I'm hiding everything....even the values that have no nulls. I'm confused can you display a visual of what you mean?

                                • 13. Re: Sorting N/A's
                                  Jian Wang

                                  I believe what was suggested is to have one hidden column for real sorting with -999... valur for NULL and another column for display with N/A value for NULL

                                  • 14. Re: Sorting N/A's
                                    Orlando Suarez

                                    Must be the fact that I was up till 3 AM last night working but I can't visualize the idea. Can you provide an example?

                                    1 2 Previous Next