topic Re: Date() format with ' in QlikView

YearMonth format with '

Tue, 02 Jun 2015 11:22:14 GMT

I need to show a date format where ' is part of the format, and I need to show only the first letter of the Month.

So 2015 January should be 15'J, and both Marts and May is M.

Re: Date() format with '

fvelascog72 — Tue, 02 Jun 2015 11:28:45 GMT

Hi,

Try:

Date(Date,'YY')&Chr(39)&left(upper(Date(Date,'MMM')),1)

Re: YearMonth format with '

anat — Tue, 02 Jun 2015 11:31:18 GMT

how you will differentiate for January ,June and July records,for all these three months output like 15'J ,15'J,15'J.

Re: YearMonth format with '

robert_mika — Tue, 02 Jun 2015 11:31:39 GMT

Depends on your data format:

=MID(DATE(Date#('01/01/2015','DD/MM/YYYY'),'DD/MMM/YYYY'),4,1)&Chr(39)&RIGHT(DATE(Date#('01/01/2015','DD/MM/YYYY'),'DD/MMM/YYYY'),2)

Re: YearMonth format with '

Ralf-Narfeldt — Tue, 02 Jun 2015 11:33:33 GMT

It seems a bit strange to not be able to differ between March and May.

Date(MyDate, 'YY''M') would give 15'1. You need to put an extra guote to escape.

Re: YearMonth format with '

Tue, 02 Jun 2015 11:38:05 GMT

Try this

=Right('2015',2)&chr(39)&left(Date('2015-01-01','MMM'),1)

Re: YearMonth format with '

martin_dideriks — Tue, 02 Jun 2015 11:39:08 GMT

If you do this, then use a dual to make sure that you will be able to:

1/ Sort the data correctly

2/ Differentiate between January, June and July and March and May

Dual(

Date(Date,'YY') & chr(39) &Upper(Left(Date(Date,'MMM'),1))

,MonthStart(Date)

)

//Martin

Re: YearMonth format with '

Tue, 02 Jun 2015 11:41:08 GMT

Yes indeed that is a problem. It will be used in a chart as dimension so you can see it from the positon relative to the others.

Re: YearMonth format with '

hic — Tue, 02 Jun 2015 11:51:47 GMT

If you use the Dual function, you can get them in the right order, so that it is obvious which month the letter refers to. Use

Dual(Left(Date(Date,'YY''MMM'),4), MonthStart(Date)) as Month

Note that two consecutive single quotes is interpreted as one single quote.

HIC

Re: YearMonth format with '

Fri, 05 Jun 2015 22:31:51 GMT

Thx for the "two consecutive" trick. I do get the same result as the version first suggested.

Re: YearMonth format with '

pratap6699 — Sat, 06 Jun 2015 14:08:09 GMT

=Date(month,'YY') & Chr(39) & Upper(Left(Month(Date(month,'MMM')),1))