Select 14 Week Ago

[#1]

Hi i have a question to get 14 weeks before today's week.

i load the data like this.

LOAD *

FROM

a.qvd

(qvd)

Where num(yearweek) >= num(year(Today())&week(Today())) -13

and num(yearweek) <= num(year(Today())&week(Today()))

;

The problem is in where condition --> num(yearweek) >= num(year(Today())&week(Today())) -13

Actually this is okay for some cases. But i realize that if today's year week is 201501 until 201513, when i minus yearweek for example 201501 with 13 it become 201488.

But maximum week of 2014 is 52. So i expect that the result are,

201501, 201452, 201451, 201450, 201449, 201448, 201447, 201446, 201445, 201444, 201443, 201442, 201441

i really need your help.

thanks

[sunny_talwar]

May be this:

LOAD *

FROM

a.qvd

(qvd)

Where num(yearweek) >= num#(year(Today())&week(Today()), '##') -13

and num(yearweek) <= num#(year(Today())&week(Today()), '##')

[#3]

Hi Sunny,

thanks for your answer,

but when i try num#(201501, '##') -13

it still return 201488, the result i expect is 201440, because the maximum week of 2014 is 52.

it's ok since today's week is more than 13, but the problem is when current week is less then 14

do you know the solution ?

thanks

[sunny_talwar]

Do you have a datefield in your database? May be you can go back 13 times 7 days (91 days) from your date field to go back 13 weeks?

[sunny_talwar]

[#6]

Hi Sunny,

No i don't, I only have yearweek, year, and week.

Is it possible to make it ?

Thanks

[sunny_talwar]

You can create date from Year and Week using MakeWeekDate() function:

MakeWeekDate(Year, Week) as Date

May be use it like this:

LOAD *

FROM

a.qvd

(qvd)

Where Num(MakeWeekDate(Year, Week)) >= Num(Today()) - 98

and Num(MakeWeekDate(Year, Week)) <= Num(Today();

Updated this after realizing that we needed to go back 14 weeks from Jyothish Kc‌ response. Thanks to him

[jyothish8807]

Hi Indra,

Do you have a date field? If yes then

Try like this:

LOAD *

FROM

a.qvd

(qvd)

Where num(Date) >= num(Today())-98;

[sasiparupudi1]

example

=weekstart(MakeWeekDate(left('201444',4),right('201444',2)),-14)

=weekstart(MakeWeekDate(left('201513',4),right('201513',2)),-14)

replace

201444' with yearWeek field

hth

Sasi

[jonathandienst]

I suggest that you convert the year and week values into date values. Trying to do temporal analysis without a date field is like trying to paddle a canoe without a paddle...