topic Re: String to month name in QlikView

String to month name

Fri, 03 Apr 2015 07:50:21 GMT

Hi all,

I want to convert a date to month number + year (e.g., jan-2014), but I cannot get it to work. My dates are in the following format: 20140130. My script looks like this:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='D-M-YYYY';

SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

LOAD [Transaction No.],

Custodian,

Date(Date#([Trade date],'YYYYMMDD'),'D-M-YYYY') as [Trade date],

month([Trade date]) as Month,

My intention here was to convert the date to the format D-M-YYYY and then to read the month from the new format, but when I try it like this I get other months displayed. Can anyone provide a solution for this? Thanks!

Re: String to month name

Michiel_QV_Fan — Fri, 03 Apr 2015 07:52:48 GMT

You are almost there:

month(Date#([Trade date],'YYYYMMDD')&'-'&year(Date#([Trade date],'YYYYMMDD') as [Trade date], (jan-2014)

num(month(Date#([Trade date],'YYYYMMDD'), '00')&'-'&year(Date#([Trade date],'YYYYMMDD') as [Trade date], (01-2014)

num(month(Date#([Trade date],'YYYYMMDD'), '0')&'-'&year(Date#([Trade date],'YYYYMMDD') as [Trade date], (1-2014)

Re: String to month name

Fri, 03 Apr 2015 08:05:27 GMT

Hi,

it works! Thanks! Is it also possible to link this MonthYear with the underlying date? I want to keep my dates and want to perform a drill down in a graph so that when clicking on a bar with MonthYear, I get to see the underlying dates. My script now looks like this:

LOAD [Transaction No.],

Date(Date#([Trade date],'YYYYMMDD'),'D-M-YYYY') as [Trade date],

month(Date#([Trade date],'YYYYMMDD'))&'-'&year(Date#([Trade date],'YYYYMMDD')) as [MonthYear],

Re: String to month name

Michiel_QV_Fan — Fri, 03 Apr 2015 08:05:54 GMT

Not exactly what you need?