Sorting problem

[marek7700]

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?

[Lisa_P]

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.

[big_dreams]

Yes As Lisa suggested your date field column two datatypes text i.e. text and date format.

use text() before your fieldName.

Regards,

[ahmar811]

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.

Regards

Ahmar

[marek7700]

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],