topic Re: Select 14 Week Ago in QlikView

Select 14 Week Ago

Wed, 02 Sep 2015 06:19:14 GMT

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

Re: Select 14 Week Ago

sunny_talwar — Wed, 02 Sep 2015 06:26:08 GMT

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()), '##')

Re: Select 14 Week Ago

Wed, 02 Sep 2015 06:31:52 GMT

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

Re: Select 14 Week Ago

sunny_talwar — Wed, 02 Sep 2015 06:34:54 GMT

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?

Re: Select 14 Week Ago

sunny_talwar — Wed, 02 Sep 2015 06:37:33 GMT

Re: Select 14 Week Ago

Wed, 02 Sep 2015 06:43:55 GMT

Hi Sunny,

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

Is it possible to make it ?

Thanks

Re: Select 14 Week Ago

sunny_talwar — Wed, 02 Sep 2015 06:49:23 GMT

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

Re: Select 14 Week Ago

jyothish8807 — Wed, 02 Sep 2015 07:01:23 GMT

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;

Re: Select 14 Week Ago

sasiparupudi1 — Wed, 02 Sep 2015 07:44:20 GMT

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

Re: Select 14 Week Ago

jonathandienst — Wed, 02 Sep 2015 07:53:09 GMT

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...

Re: Select 14 Week Ago

sunny_talwar — Wed, 02 Sep 2015 07:54:39 GMT

I love your Canoe example

Re: Select 14 Week Ago

qlikviewwizard — Wed, 02 Sep 2015 08:14:33 GMT

Hi,

Please use this script.

Thank you.

SET DateFormat='DD-MMM-YYYY';

//SET DateFormat='YYYYMM';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Employee:

load *, year(date(HIREDATE)) as Year,week(date(HIREDATE)) as Week;

load EMPNO, ENAME, JOB, MGR, date(HIREDATE) as HIREDATE, SAL, COMM, DEPTNO inline [

EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

7369, SMITH, CLERK, 7902, 2015-12-17 00:00:00.000, 800.00, NULL, 20

7499, ALLEN, SALESMAN, 7698, 2015-02-20 00:00:00.000, 1600.00, 300.00, 30

7521, WARD, SALESMAN, 7698, 2015-02-22 00:00:00.000, 1250.00, 500.00, 30

7566, JONES, MANAGER, 7839, 2015-04-02 00:00:00.000, 2975.00, NULL, 20

7654, MARTIN, SALESMAN, 7698, 2015-09-28 00:00:00.000, 1250.00, 1400.00, 30

7698, BLAKE, MANAGER, 7839, 2015-05-01 00:00:00.000, 2850.00, NULL, 30

7782, CLARK, MANAGER, 7839, 2015-06-09 00:00:00.000, 2450.00, NULL, 10

7788, SCOTT, ANALYST, 7566, 2015-12-09 00:00:00.000, 3000.00, NULL, 20

7839, KING, PRESIDENT, NULL, 2015-11-17 00:00:00.000, 5000.00, NULL, 10

7844, TURNER, SALESMAN, 7698, 2015-09-08 00:00:00.000, 1500.00, 0.00, 30

7876, ADAMS, CLERK, 7788, 2015-01-12 00:00:00.000, 1100.00, NULL, 20

7900, JAMES, CLERK, 7698, 2015-12-03 00:00:00.000, 950.00, NULL, 30

7902, FORD, ANALYST, 7566, 2015-12-03 00:00:00.000, 3000.00, NULL, 20

7934, MILLER, CLERK, 7782, 2015-01-23 00:00:00.000, 1300.00, NULL, 10

];

Employee_Final:

load EMPNO, ENAME, JOB, MGR,HIREDATE,SAL, COMM, DEPTNO,Year,Week

Resident Employee

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

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