2 Replies Latest reply: Nov 4, 2015 3:49 AM by Henric Cronström RSS

    Correct order in Qlik Sense

    Miguel Braga

      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:

       

      Sem Título.png

      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; stalwar1; jontydkpi; robert_mika; swuehl, anyone, help?


      Regards,

      MB




        • Re: Correct order in Qlik Sense
          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?

          • Re: Correct order in Qlik Sense
            Henric Cronström

            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