Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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()), '##')
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
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?
Hi Sunny,
No i don't, I only have yearweek, year, and week.
Is it possible to make it ?
Thanks
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
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;
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
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...