Correct order in Qlik Sense

[miguelbraga]

I have a field in the following format - YYYYMM (ex: 200801) and I need monthName in the format - MMM-YYYY (ex: Jan-2008)

What I've done is this:

date(makedate(ano, num(mes, '00'), 01),'MMM YYYY') as MMMYYYY,

Pick(mes, 'jan','fev','mar','abr','mai','jun','jul','ago','set','out','nov','dez') as dsc_mes

The problem here is that the months doesn't seem to appear correctly ascending sorted. For example, in the year 2014 the months appear in the correct order, but in the year 2015 which there isn't all the possible months, the chart appear like this:

I've tryed format the field like I would do in Qlikview, but the field becomes null:

date(monthstart('01-' & right(cod_anomes, 2) & '-' & left(cod_anomes, 4)),'MMM-YYYY') as MMMYYYY,
month(date(monthstart('01-' & right(cod_anomes, 2) & '-' & left(cod_anomes, 4)))) as dsc_mes

What I'm doing wrong? What can I do to solve this problem? Please I need help, from you experts.

gwassenaar‌; hic‌; sunindia‌; jontydkpi‌; Robert_Mika‌; swuehl‌, anyone, help?

Regards,

MB

[Gysbert_Wassenaar]

I'm afraid your post is confusing me. I see field names like MMMYYY and dsc_mes mention, but the screenshot seems to use a field mes_PCO. And that shows two-digit values that look like month numbers and not MMM-YYYY values. The description you wrote and the screenshot you posted don't make sense (no pun intended).

Did you change the sort order of the dimension at all or is it set to automatic? Perhaps you simply need disable the autormatic sorting and to set it to sort numerically.

Can you post a small qlik sense app that demonstrates the problem?

[hic · Accepted solution]

Using the Pick() function is not a good idea - you should use the built-in date and time functions instead.

If you have a month field "cod_anomes" with the format YYYYMM, you should simply interpret this correctly in the script using

     Date(Date#(cod_anomes,'YYYYMM'),'MMM-YYYY') as AnoMes

AnoMes will be correctly sorted, and you can use it in further transformations.

HIC