5 Replies Latest reply: Jun 26, 2012 8:00 AM by Hanna Ottosson RSS

    Keep sort order on selections

    Hanna Ottosson

      I want to present a straight table with headlines as dimension and sum(amount) as expression. I want to show all values of the headlines even though there are no amounts associated with all headlines. I have ticked show all values under the dimension. So far so good. The problem is that I want to sort the order of the headlines in a specific way, according to another field, Sort, which is a numeric field with numeric values. So in the sort tab I have ticked sort by expression, and put the field Sort and ascending. However when I make a selection, for example a date, the sort order changes. I can still see all values of the headlines, but the ones without amount on top, and the ones with amount in the bottom. What can I do to keep the sort order even when I make selections?

       

      I have also tried to use, Load Order original in the Sort tab. The field sort is not sorted in ascending or descending in the script, so I user order by Sort in the script, but that doesn’t do anything. Like this

       

      Test:

      NoConcatenate LOAD

                *

      Resident Testtmp

      Order by  Sort asc;

       

       

      DROP Table Testtmp;

       

      The field Sort doesn't get sorted in numeric ascending order. It still shows the values in random order.

        • Re: Keep sort order on selections
          Miguel Angel Baeyens de Arce

          Hi Hanna,

           

          You will need to add a order field, in your case the function RecNo() will do

           

          Test:
          NoConcatenate LOAD
               *,
               RecNo() AS OrderNo
          Resident Testtmp
          Order by Sort asc;
          
          DROP Table Testtmp;
          

           

          Once the script is reloaded, you can sort by this field in the Expression textbox.


          Hope that helps.

           

          Miguel

            • Re: Keep sort order on selections
              Hanna Ottosson

              Hi, thanks for the replies.

              Miguel, it both worked and didn't work. It did the sorting right, but when I put OrderNo in the straight table under expression in the sort tab, it still does the sorting in random order, placing 275 first instead of one. I'm not sure why.

              In the Sort field I have several occurrences of the same value, i.e. the value 2345 can occur 5 times. But that shouldn't really matter?

               

              Sal, there are too many values to use the match function.

               

              / Hanna

                • Re: Keep sort order on selections
                  Miguel Angel Baeyens de Arce

                  Hi Hanna,

                   

                  There are some cases in charts where the sort may be different or not applicable, for example if you are using the TOTAL qualifier in some of your expressions. Check in the Sort tab of the chart properties and promote / demote the columns to get the priority you want.

                   

                  If you are using the OrderNo as expression, uncheck all the other sort options, and leave only the expression ascending or descending.

                   

                  Hope that helps.

                   

                  Miguel

              • Re: Keep sort order on selections

                Hi Hanna,

                 

                if i understand you correctly then you can use the Match function ...

                got to the chart properties and select only sort by expression and ascending . in the expression statement use match function like

                 

                match(column,' value1', ' value2',...)

                 

                hope this will help.

                Regards

                Sal1