4 Replies Latest reply: Jul 4, 2018 10:41 AM by Marek Adamczyk RSS

    Sorting problem

    Marek Adamczyk

      I have a problem sorting GL Account in Pivot Table and Regular Table. I tried a few things so far:

      1. Used sorting numerically and alphabetically in the chart.

      2. Used Order By in SQL query in the load script.

      3. Added Text and Trim function in the script (Text(RTrim(SegValue1)) & '-' & Text(RTrim(SegValue2)) & '-' & Text(RTrim(SegValue3)) as [GL Account]).


      In the table the account 1001-10-10 is in completly different place than the other two accounts and when I search the third GL account is adjusted to the right (see below). What am I missing?


        • Re: Sorting problem
          Lisa Phillips

          Typically, text or string characters will be shown as left aligned and numbers as right aligned, so your data appears to be in two different formats.  If it was all in one format it would make it easier to sort.

          • Re: Sorting problem
            Ahmar Ansari

            Hi Mark,


            Please convert your field expression into Text or Num


            like below

            for Number

            num((Text(RTrim(SegValue1)) & '-' & Text(RTrim(SegValue2)) & '-' & Text(RTrim(SegValue3))) as [GL Account]

            for Text

            Text((Text(RTrim(SegValue1)) & '-' & Text(RTrim(SegValue2)) & '-' & Text(RTrim(SegValue3))) as [GL Account]

            then itw work for your sorting.



            • Re: Sorting problem
              Marek Adamczyk

              Thank you all for your suggestions. I've put the Text function in front of all segments and it worked.


              This works: Text(SegValue1 & '-' & SegValue2 & '-' & SegValue3) as [GL Account],

              This doens't work: Text(Text(SegValue1) & '-' & Text(SegValue2) & '-' & Text(SegValue3)) as [GL Account],