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]

I love your Canoe example

[qlikviewwizard]

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());