Sorting Issues

[akpofureenughwu]

Good morning everyone,

I have been having issue sorting lately...

I used the script:

Match ([Repair.Month], 'January','February','March','April','May','June','July','August','September','October','November','December')

But stilll the data is not sorted by month,,,,

Please kindly help me review it.

Regards

See attached

[ecolomer]

Are you sure that the "Repair Month" field has a date format?

It is possible the reason for the behaviour you experienced is that you already loaded your month_fullnames as a date field formatted as month fullnames. So the proposed solutions using the Date# function didn't find a string to interpret as a date (that's what the #-functions do).

On the other hand this means, that your front end expression

1. =Month(Date(Month_FullNames,'MMM')) 

can be abbreviated to

1. =Month(Month_FullNames) 

as the formatting date() function is useless in this case, it does not change the underlying numerical value of your Month_FullNames field (Repair Month).Using this expression

1. Date(Date#(SubField('$(MonthNames)',';',MONTH),'MMM'),'MMMM') as [Repair Month] 

to create your [Repair Month] field in the script leads to somewhat weird underlying numerical values:

because you are trying to create a complete date value from only a month field (the year information is missing) and then just formatting it to show month fullnames.I therefore stick to my proposed solution

1. Dual(Date(Date#(MONTH,'MM'),'MMMM'), MONTH) as [Repair Month]

which creates these underlying values:

like one would expect from a real month field. If you wanted to create the month shortname field in the script, then one solution could be:

1. Month(Date#(MONTH,'MM')) as [Repair Month]_short 

hope this helps

[priyasawant]

Hi.. kindly Keep repair month on 1st and then Number Of service Order in sorting tab

[HirisH_V7]

Hi,

Use this expression in the sort and Keep Ascending,

=Num(Month((Date#(Month,'MMM'))))

HTH,

Hirish

[akpofureenughwu]

Hello sir,

Thank you for your response. Still have issue sort out the month....

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET CollationLocale='en-GB';

SET CreateSearchIndexOnReload=1;

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Here is the script

LIB CONNECT TO [sql];

Qualify*;

['store']:

LOAD

    [Item Supplied],

    [Year ] AS [Year],

    [Month],

    [Amount];

SQL SELECT "Item Supplied",

    "Year ",

    "Month",

    "Amount"

FROM "Admin"."dbo"."'store'";

[Repair]:

LIB CONNECT TO 'sql';

LOAD Date(Date#(SubField('$(MonthNames)',';',"Month"),'MMM'),'MMMM') as [Repair Month],

    "Year",

    "No of Service Order",

    "              Amount";

SQL SELECT "Month",

    "Year",

    "No of Service Order",

    "              Amount"

FROM Admin.dbo."'Repair of Office Equipment $'";

[akpofureenughwu]

That's the format I arranged it. I don't think it will make a difference

[akpofureenughwu]

I just checked the data type of  month. (repair_month) is not a date format..

[rahulpawarb]

Please use given expression under the sort tab - sort by expression (deselect all other options):

=Date([Repair Month],'MM')

Regards!

Rahul

[akpofureenughwu]

Hello Rahul

Thank you for your response.

The =Date([Repair Month],'MM') expression was helpful, it helped me to sort 0ut the month data. I tried using the saME expression for the month field on the table but it didn't work.

Please find the attached file for clarity

Thank you ! 

[rahulpawarb]

Hello Akpofure,

Please share the application with sample data. This will help to further analyze this issue.

Regards!
Rahul