topic Re: Convert YYYY-WW to Date in QlikView

Convert YYYY-WW to Date

Mon, 02 Sep 2013 07:17:56 GMT

Hello,

Is it possible to convert a YYYY-WW date format into a single date? Lets say the last day of that week?

Re: Convert YYYY-WW to Date

tresB — Mon, 02 Sep 2013 07:29:43 GMT

Use MakeWeekDate() function.

Example:

=MakeWeekDate(Left('2013-09',4),Right('2013-09',2),6) // the red '6' tells about the last day of the week, you can modify accordingly.

Re: Convert YYYY-WW to Date

kaushiknsolanki — Mon, 02 Sep 2013 07:32:20 GMT

Hi,

Yes. Consider that the YYYY-WW is the text then you can use the below expression to get the date.

MakeWeekDate(left(Field,4),right(field,2),0)

Here 0 is considered as monday of that week.

Regards,

Kaushik Solanki

Re: Convert YYYY-WW to Date

orital81 — Mon, 02 Sep 2013 07:35:40 GMT

Here is one option (vWeek should be a date in YYYY-WW format):

MakeWeekDate(Left(vWeek,4),Right(vWeek,2))

Re: Convert YYYY-WW to Date

Mon, 02 Sep 2013 07:53:06 GMT

Thank you. I have one problem. Some fields are like this:

2013-2

instead of 2013-02.

Is there a way to exclude the '-' ?

Re: Convert YYYY-WW to Date

kaushiknsolanki — Mon, 02 Sep 2013 07:55:10 GMT

Hi,

In that case you can try this.

MakeWeekDate(left(Field,4),if(len(Field)=7,right(field,2),right(field,1)),0)

Regards,

Kaushik Solanki

Re: Convert YYYY-WW to Date

kaushiknsolanki — Mon, 02 Sep 2013 08:00:11 GMT

Hi, Tresosco,

suggestion is not bad but num() will not work in this case, but num#() will work.

Regards,

Kaushik Solanki

Re: Convert YYYY-WW to Date

orital81 — Mon, 02 Sep 2013 08:00:57 GMT

Yes there is:

MakeWeekDate(SubField('2013-02','-'),SubField('2013-02','-',2))

Re: Convert YYYY-WW to Date

rbecher — Mon, 02 Sep 2013 08:01:41 GMT

Would be a negative week number:

MakeWeekDate(left('2013-2',4),Num#(right('2013-2',2)),0) => 10.12.2012

Re: Convert YYYY-WW to Date

tresB — Mon, 02 Sep 2013 08:02:38 GMT

, right .

Re: Convert YYYY-WW to Date

kaushiknsolanki — Mon, 02 Sep 2013 08:04:36 GMT

Ralf,

you are also right.

Better you use the solusion i have suggested with checking the lenth, you can also try the solution given by Ori.

Regards,

Kaushik Solanki

Re: Convert YYYY-WW to Date

jagan — Mon, 02 Sep 2013 08:04:44 GMT

Hi,

Try like this

=MakeWeekDate(left('2013-02',4), Num(SubField('2013-02', '-', 2)))

Regards,

Jagan.

Re: Convert YYYY-WW to Date

rbecher — Mon, 02 Sep 2013 08:06:33 GMT

Or subfield:

=MakeWeekDate(subfield('2013-2','-',1),subfield('2013-2','-',2),0)