9 Replies Latest reply on Nov 27, 2018 9:48 AM by Jennifer VonHagel

    Forced Sorting

    Katherine Dinsmore

      Hi everyone,

      I am wondering if anyone knows how to include a line in this source code to force the Sort order

       

      IF [Question Set ID] = "1" THEN

          CASE [Response]

              WHEN 1 THEN "Started Here"

              WHEN 2 THEN "Started Elsewhere"

          ELSE "Unknown" END

      ELSEIF [Question Set ID] = "2" THEN

          CASE [Response]

              WHEN 1 THEN "Less than full-time"

              WHEN 2 THEN "Full-time"

          ELSE "Unknown" END  

      ELSEIF [Question Set ID] = "3" THEN

          CASE [Response]

              WHEN 1 THEN "Yes"

              WHEN 2 THEN "No"

          ELSE "Unknown" END  

      ELSEIF [Question Set ID] = "4" THEN

          CASE [Response]

              WHEN 1 THEN "Never"

              WHEN 2 THEN "Sometimes"

              WHEN 3 THEN "Often"

              WHEN 4 THEN "Very Often"

          ELSE "Unknown" END

      END

       

      I have numerous questions in a pivot dimension, each which has a different set of responses so using the manual sort feature isn't practical. The problem now is that the response items (regardless of how I have them ordered in the ELSEIF statement(s)) just sort randomly and I need a way to get them in the proper order.

       

      Thank you!

      Kate

        • 1. Re: Forced Sorting
          rao marapatla

          Hi Could you please share workbook here so that we can help you in right way

          • 2. Re: Forced Sorting
            Peter Fakan

            Hi Katherine,

             

            I think they are alphanumerically sorted, e.g.

             

            IF [Question Set ID] = "1" THEN

                CASE [Response]

                    WHEN 1 THEN "1. Started Here"

                    WHEN 2 THEN "2. Started Elsewhere"

                ELSE "3. Unknown" END

             

            //should evaluate 'Started Here' before 'Started Elsewhere' even though E comes before H

             

            ELSEIF [Question Set ID] = "2" THEN

                CASE [Response]

                    WHEN 1 THEN "1. Less than full-time"

                    WHEN 2 THEN "2. Full-time"

             

            //should evaluate 'Less than full-time' before 'Full-time' even though F comes before L

             

            HTH

             

            Peter

            • 3. Re: Forced Sorting
              Katherine Dinsmore

              Hi Peter,

              I think you're right. Great work around but I'm wondering if there is a way to accomplish the same thing without adding numerical values to the responses? Or, somehow use the number for sorting purposes and then display a different value (i.e. alias). I would prefer not to include the numbers if at all possible.

               

              Thank you! Kate

              • 4. Re: Forced Sorting
                Peter Fakan

                try r/clicking on the calculated field in the measures/dimensions area, then select Default Properties > Sort

                 

                I can't recall if I've ever done this for a calculated field, but its how I re-sort values in a viz.

                 

                 

                HTH

                 

                Peter

                • 5. Re: Forced Sorting
                  Katherine Dinsmore

                  Sadly, nope. The problem with that approach is that it gives me every possible value from all questions (there are nearly 40 in all) so even if I tried to manually sort that list I don't think it would work. I was hoping I could use the numbering tactic and then create a new alias -- something like (even though I know AS won't work):

                   

                  ELSEIF [Question Set ID] = "5" THEN

                      CASE [Response]

                          WHEN 1 THEN "1. Very Little" AS "Very Little"

                          WHEN 2 THEN "2. Some" AS "Some"

                          WHEN 3 THEN "3. Quite a Bit" AS "Quite a Bit"

                          WHEN 4 THEN "4. Very Much" AS "Very Much

                      ELSE "Unknown" END

                   

                  But I don't know the programming side of it well enough [yet] to figure out how to do that [grin].

                   

                  Kate

                   

                  P.S. If I end up using the numerical approach, I'll circle back and tag it as the Correct Answer. Cheers.

                  • 6. Re: Forced Sorting
                    Jennifer VonHagel

                    HI Katherine,

                     

                    I can think of two options.  Check out this simple example:

                    You can put the column to sort by as the first field on the row or column. This will force a sort by this column.

                    And then you can hide the first column by un-checking "Show Header":

                     

                    Or better yet, use ID to sort Description directly: Use the dropdown arrow on the Description pill. Go to Sort.

                     

                    Sort by "Field" and choose ID for the Field Name. This has to be aggregated in some way; I would just put max or min.  Because there is a one to one relationship between ID and Description (as I believe there will be for your sort order and field), Max or Min will give the correct result.

                     

                     

                    And you can see it is sorted.

                     

                    I hope this helps.

                     

                    Best,

                    Jennifer

                    • 7. Re: Forced Sorting
                      Jennifer VonHagel

                      Also, it kind of looks like you could use your Response field to sort by - it seems to be numbered correctly.  But if not, You could calculated a Sort Field by copying your IF statement above, and instead of response strings, put the sort number you want associated with that Response.

                       

                      Best,

                      Jennifer

                      • 8. Re: Forced Sorting
                        Katherine Dinsmore

                        Jennifer,

                         

                        Thank you so much for this suggestion! Creating a new calculated field used specifically for sorting did the trick Joy.

                         

                        Cheers, Kate

                        • 9. Re: Forced Sorting
                          Jennifer VonHagel

                          You're welcome!