Sort data of month

[nsigitsutanto]

i have data which consists of series of month. I dont know how to sort the as a series, and it keeps sorting alphabetically, so instead of January, February, March...., it is still keep sorting like April, February, January.
How to solve this? thanx

[tresesco]

It seems that your month field is a text field. You can sort these values by expression : =date#(Month, 'MMMM')

[JordyWegman]

Hi,

The reason for this is because Qlik handles it like a string, not as a date.

Couple of questions and possible solutions:

1. Do you have a numeric field of the months? Like 1,2,3 etc? 
   1. Then you can sort on this field or make a dual() field of the month and numeric field (sort then on the numeric field)
2. Do you have a date value for this month?
   1. Then you get the Date(month([YourDateField],'MMMM') function to get the month name and then it will sort automatically.
3. Is the month unique or do you have multiple months?
   1. You can create a MonthSequenceNumber for this so that every month year combination has an unique number.

If you don't have that, you do a mapping with an inline table:

mapNumericMonth2Month:
Mapping Load * Inline [
January, 1
February, 2
March, 3
April, 4
May, 5
June, 6
July, 7
August, 8
September, 9
October, 10
November, 11
December, 12
];

YourTableWithFacts:
Load
   *,
   ApplyMap('mapNumericMonth2Month',Month,0) as NumericMonth
From [YourSource];

Jordy

Climber

[nsigitsutanto]

where do i fill the data field?

[JordyWegman]

You can do this in the script, or do you work in the data manager?

Jordy

Climber

[nsigitsutanto]

where do I write this code? Actually I want to sort the month data as filter pane, so then I write this in the expression of the filter pane or where?

[JordyWegman]

That depends on where you want to solve your problem. But taking the option of @tresesco , you can fill this in your filterpane if you want to, just enter =Date#(Month('Month'),'MMMM')

Better would be to do this in the script editor of Qlik Sense. This way, you don't have to fix this everytime you want to use your month field. Do you know how you can do this?

Jordy

Climber

[nsigitsutanto]

actually i have tried what @tresesco said, but it still cant work
i have done this
=Date#(Month([Sheet1.bln.ing]),'MMMM')
[Sheet1.bln.ing] is the month data

actually, i'm still new to qlik sense and dont know how to use the script editor, but i have understand how to use the expression function in the chart. So then, i prefer to do what @tresesco said.
anyway, the code that u have written, is it to be written in the script editor or expression of the filter pane? and about [YourSource], is it filled with the month data (in my case is [Sheet1.bln.ing])? because i have tried doing this in script editor, but it didnt work.
Thanx in advance anyway

[JordyWegman]

If you have loaded this Excel in Qlik Sense via the Data Manager, there should be a locked script. You can unlock this by clicking 'unlock' in the top right corner.

Then you will see your script and your 'Month' field. Replace this field in your script with the following line:

Date#(Month([Sheet1.bln.ing]),'MMMM') as Month,

Check if you need the comma at the end, you don't need it if this is last field in the table.

Jordy

Climber 

[tresesco]

Month() is a formatting function that won't work on string/text values. Try removing month() and just like a proposed above:
=Date#([Sheet1.bln.ing],'MMMM')

If this doesn't work, try to share a sample app to work on