Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
akpofureenughwu
Creator III
Creator III

Sorting Issues

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

15 Replies
ecolomer
Master II
Master II

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
Creator II
Creator II

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

Repair.PNG

HirisH_V7
Master
Master

Hi,

Use this expression in the sort and Keep Ascending,

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

HTH,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
akpofureenughwu
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

akpofureenughwu
Creator III
Creator III
Author

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

rahulpawarb
Specialist III
Specialist III

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

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

Regards!

Rahul

akpofureenughwu
Creator III
Creator III
Author

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
Specialist III
Specialist III

Hello Akpofure,

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

Regards!
Rahul