topic Convert Date format to Month in QlikView

Convert Date format to Month

Fri, 19 Sep 2014 09:54:17 GMT

Hi,

I have a format like 17/12/2012 19:35:38 hours, i want only Month in bar chart.

Re: Convert Date format to Month

Anonymous — Fri, 19 Sep 2014 09:56:33 GMT

You could use the MonthName() function in a Calculated Dimension in Bar Chart.

If possible though it would be better to create a MonthName field in your load script.

Re: Convert Date format to Month

amit_saini — Fri, 19 Sep 2014 09:56:39 GMT

Vamsi,

Try this :

Monthname(Your_Date_Field) as Month

and call Month as dimension in bar chart.

Thanks,

Re: Convert Date format to Month

anbu1984 — Fri, 19 Sep 2014 09:56:52 GMT

=Month(Timestamp#('17/12/2012 19:35:38','D/M/YYYY hh:mm:ss'))

Re: Convert Date format to Month

Fri, 19 Sep 2014 09:57:33 GMT

HI,

If I catch your issue correctly, I believe you can achieve what you seek just by using MONTH( Date ), either as a new field in your script (recommended), or as a Calculated Dimension in your chart.

Re: Convert Date format to Month

amit_saini — Fri, 19 Sep 2014 10:01:23 GMT

See the attachment.

Thanks,
AS

Re: Convert Date format to Month

Fri, 19 Sep 2014 10:05:03 GMT

Use this it will solve your problem

=Month(Date(SubField(Timestamp(DATE_FIELD,'YYYY-MM-DD hh:mm:ss'),' ',1),'DD/MM/YYYY'))

Re: Convert Date format to Month

MK_QSL — Fri, 19 Sep 2014 10:05:26 GMT

=Month(Timestamp#('17/12/2012 19:35:38','DD/MM/YYYY hh:mm:ss'))

=Date(MonthStart(Timestamp#('17/12/2012 19:35:38','DD/MM/YYYY hh:mm:ss')),'MMM YYYY')

=DUAL(Date(MonthStart(Timestamp#('17/12/2012 19:35:38','DD/MM/YYYY hh:mm:ss')),'MMM YYYY'),Date(MonthStart(Timestamp#('17/12/2012 19:35:38','DD/MM/YYYY hh:mm:ss')),'YYYYMM'))

Re: Convert Date format to Month

Fri, 19 Sep 2014 10:35:29 GMT

Thanks a lot for all your help.

Re: Convert Date format to Month

MarcoWedel — Fri, 19 Sep 2014 22:26:56 GMT

Hi,

please specify your requirements, because there are two possibilities:

1. you want the same number of bars and just replace the displayed timestamp with a month, i.e. there will be multiple occurences of each month.

2. you want to aggregate by months, i.e. the number of bars will decrease and each month will only occur once.

solutions like

Date(Date, 'MMM')

will not change the underlying numerical value of your date field but simply format the displayed text and thus keep the different bars untouched (case 1).

solutions like

Month(Date)

will change the underlying numerical value to the month number (1-12) and aggregate all dates within this month.

hope this helps

regards

Marco

Re: Convert Date format to Month

Gabriel — Sat, 20 Sep 2014 01:03:40 GMT

Hi Vamsi,

Please try this and just pick the field for your dimension.

OUTPUT:

LOAD

,Date(Floor(Timestamp#(DATEFIELD, 'DD-MM-YYYY hh:mm:ss')), 'DD/MM/YYFYY') AS NewDate

,Month(Floor(Timestamp#(DATEFIELD, 'DD-MM-YYYY hh:mm:ss'))) AS JustMonth

Resident TableName

;

Best Regards,

Gabriel