topic Re: Re: Date formatting in QlikView

Date formatting

Fri, 24 Oct 2014 08:42:03 GMT

Im trying to format the following field [0SERV_DATE].[20130401] into 'YYYY-MM'.

I managed to format it into a date using:

date(makedate(left(left(right([Serv. Rendered],9),8),4),

mid(left(right([Serv. Rendered],9),8),5,2),

right(left(right([Serv. Rendered],9),8),2)),'YYYY-MM-DD') as ServiceRenderedDate

but when i try formating this into 'YYYY-MM' using ...

date(ServiceRenderedDate,'YYYY-MMM') as ServiceRenderedMonth

i get for example ...

2013-Apr

Any suggstions will be appreciated.

Regards,

Magen

Re: Date formatting

anbu1984 — Fri, 24 Oct 2014 08:45:34 GMT

Date(Date#('20130401','YYYYMMDD'),'YYYY-MMM') --2013-Apr

Date(Date#('20130401','YYYYMMDD'),'YYYY-MM') --2013-04

Re: Date formatting

MarcoWedel — Fri, 24 Oct 2014 08:46:36 GMT

date(ServiceRenderedDate,'YYYY-MM') as ServiceRenderedMonth

Re: Re: Date formatting

MarcoWedel — Fri, 24 Oct 2014 08:48:26 GMT

better

date(MonthStart(ServiceRenderedDate),'YYYY-MM') as ServiceRenderedMonth

to get only one value for all dates in this month.

Re: Re: Date formatting

Fri, 24 Oct 2014 08:55:52 GMT

Hi Anbu

Im not quite sure about your solution ..

You placed the actual string into the date function and not the field name, even if i place in the field name like so ..

Date(Date#(ServiceRenderedDate,'YYYYMMDD'),'YYYY-MMM') as ServiceRenderedMonth

it returns no values...

Regards,

Magen

Re: Re: Date formatting

Fri, 24 Oct 2014 08:56:47 GMT

Hi Marco

Thanks for your reply but the solution returns null values ..

Regards,

Magen

Re: Date formatting

anbu1984 — Fri, 24 Oct 2014 09:01:48 GMT

What is the format of ServiceRenderedDate?

Re: Date formatting

Fri, 24 Oct 2014 09:06:11 GMT

Hi guys

No problem guys i got the solution ...

So the original field is [0SERV_DATE].[20130401]

so i scripted ..

date(date#(left(right([Serv. Rendered On - Serv. Rendered On Level 01 (Key)],9),6),'YYYYMM'),'YYYY-MM') as ServiceRenderedMonth,

and received ...

2013-04

2013-05

2013-06

2013-07

2013-08

2013-09

2013-10

2013-11

2013-12

2014-01

2014-02

2014-03

2014-04

2014-05

2014-06

2014-07

2014-08

2014-09

2014-10

Worked like a charm 🙂

Regards,

Magen

Re: Date formatting

anbu1984 — Fri, 24 Oct 2014 09:14:35 GMT

=Date(Date#(TextBetween('[0SERV_DATE].[20130401]','[',']',2),'YYYYMMDD'),'YYYY-MM')