Loading Week Year

[#1]

Hi;

I have a field called [Last Drop Plan Date Start] which l want to use to populate week year number. How do l manipulate this field to give me week year number for example week 45, week 47 etc.

Last Drop Plan Date Start

28/10/2015 00:00

28/10/2015 00:00

29/10/2015 00:00

29/10/2015 00:00

30/10/2015 00:00

30/10/2015 00:00

02/11/2015 00:00

02/11/2015 00:00

02/11/2015 00:00

Please assist.

[#2]

Please note, the field is not in date format.

[thomaslg_wq]

Hi,

First point, is it ok that these dates are not in the same format ?

30/10/2015 00:00 is text and it's october,

02/11/2015 00:00 is date and it's february.

You could use if(isnull(num([Last Drop Plan Date Start])),week(makedate(num(evaluate(left(subfield([Last Drop Plan Date Start],'/',3),4))),num(evaluate(subfield([Last Drop Plan Date Start],'/',2))),num(evaluate(subfield([Last Drop Plan Date Start],'/',1))))),week([Last Drop Plan Date Start])) as Week

But 02/11/2015 is then week 7... because 02 is february in your date format

[t_chetirbok]

Hello!

You can transform your field by string functions, like this:

week(right(left(Date,10),4)&'-'&right(left(Date,5),2)&'-'&left(Date,2))

where Date = [Last Drop Plan Date Start]

[t_chetirbok]

Or you can use this: week(date(date#([Last Drop Plan Date Start], 'DD/MM/YYYY hh:mm')))

[maxgro]

if th field is a text you can use Date# as interpretation function and then Week funtion

X:

load

  *,

  Week([Last Drop Plan Date Start]) as Week;

load

  Date#([Last Drop Plan Date Start], 'DD/MM/YYYY hh:mm') as [Last Drop Plan Date Start]

inline [

Last Drop Plan Date Start

28/10/2015 00:00

28/10/2015 00:00

29/10/2015 00:00

29/10/2015 00:00

30/10/2015 00:00

30/10/2015 00:00

02/11/2015 00:00

02/11/2015 00:00

02/11/2015 00:00

];

[MarcoWedel]

Hi,

another interpretation function that is possible:

LOAD Week(Timestamp#([Last Drop Plan Date Start],'DD/MM/YYYY hh:mm')) as [Last Drop Plan Week]
FROM [https://community.qlik.com/thread/192631] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco