4 Replies Latest reply: Jan 6, 2017 12:12 AM by Supriya R RSS

    Show Date formate in long Month Names in Qliksense

    Supriya R

      Hi,

       

      I need to show date in format like December 23rd.

       

      How could i do that?

       

       

      Regards,

      Supriya

        • Re: Show Date formate in long Month Names in Qliksense
          Gysbert Wassenaar

          You can use MMMM in a date format string for the long month names. But the 'st', 'nd', 'rd', 'nth' suffixes you want after the day numbers can't be specified in a format string. You'll have to use an expression to calculate which day number should get which suffix. If you use a master calendar table that you create in the script then you can add those suffixes to that calendar table:

           

          If(Day(MyDate)=1 or Day(MyDate) = 21, or Day(MyDate) = 31, 'st',

               If(Day(MyDate)=2 or Day(MyDate)= 22, 'nd',

                    If(Day(MyDate)=3 or Day(MyDate)=23,'rd','th'))) as Suffix

            • Re: Show Date formate in long Month Names in Qliksense
              oskars caikovskis

              Or you can do the same thing directly in the table.

              date(MyDate,'MMMM D') &
              if (
                   Match(Day(MyDate),1,21,31),
                   'st',
                   if (
                        Match(Day(MyDate),2,22),
                        'nd',
                        if (
                              Match(Day(MyDate),3,23),
                              'rd',
                              'th'
                        )
                   )
              )
              

               

              Note - this will turn the field from date format, to a string format. This might cause you some problems with sorting. For this not to happen, make sure you've selected custom sorting with MyDate as the sort order and check sort numerically.

            • Re: Show Date formate in long Month Names in Qliksense
              Anil Babu Samineni

              I faced same problem, Recently somewhat i got solve

               

              Date (Square) Problem

                • Re: Show Date formate in long Month Names in Qliksense
                  Supriya R

                  Hi Anil,

                   

                   

                  Thanks this is what i was looking for, i used below code for my requirement with help of your answer.

                   

                   

                  Pick((Match(Right(Day(Date(WeekEnd(Today()-7))),1),1,2,3) + Pick(Match(Day(Date(WeekEnd(Today()-7))),11,12,13)+1,0,-1,-2,-3))+1,

                  Date(WeekEnd(Today()-7),'MMMM DD'  & Chr(7511)&Chr(688)),

                  Date(WeekEnd(Today()-7),'MMMM DD' & Chr(738)&Chr(7511) ),

                  Date(WeekEnd(Today()-7),'MMMM DD'  & Chr(8319)&Chr(7496)),

                  Date(WeekEnd(Today()-7),'MMMM DD' & Chr(691)&Chr(7496)),

                  )