topic Derived Quarter from DATE in QlikView

Derived Quarter from DATE

qlikviewwizard — Fri, 20 Nov 2020 18:02:30 GMT

Hi Folks,

I want to derive custom quarter from the following dates.

How to achieve the same?

Thanks in advance.

Quarters need to derive from DATE column as shown below :

1-Jan-2015 to 6-Apr-2015 Then Q1

7-Apr-2015 to 5-Jul-2015 Then Q2

5-Jul-2015 to 10-Oct-2015 Then Q3

11-Oct 2015 to-3-Jan-2016 Then Q4

Script:

SALTable:

LOAD * INLINE [

EMPNO1,ENAME,JOB,INCHARGE1,INCHARGE2,DATE,SAL,DEPTNO

7369,SMITH,CLERK,7902,7566,17-Dec-2015,700,20

7369,SMITH,CLERK,7902,7566,17-Dec-2015,900,20

7499,ALLEN,SALESMAN,7698,7839,20-Feb-2015,1600,30

7499,ALLEN,SALESMAN,7698,7839,20-Feb-2015,1900,30

7521,WARD,SALESMAN,7698,7839,22-Feb-2015,0,30

7521,WARD,SALESMAN,7698,7839,22-Feb-2015,1650,30

7566,JONES,MANAGER,7839,7698,2-Apr-2015,2975,20

7566,JONES,MANAGER,7839,7698,2-Apr-2015,3975,20

7654,MARTIN,SALESMAN,7698,7788,28-Sep-2015,1250,30

7654,MARTIN,SALESMAN,7698,7788,28-Sep-2015,2250,30

7698,BLAKE,MANAGER,7839,7698,1-May-2015,2850,30

7698,BLAKE,MANAGER,7839,7698,1-May-2015,3850,30

7782,CLARK,MANAGER,7839,7566,9-Jun-2015,0,10

7782,CLARK,MANAGER,7839,7566,9-Jun-2015,3450,10

7788,SCOTT,ANALYST,7566,7782,9-Dec-2015,1250,20

7788,SCOTT,ANALYST,7566,7782,9-Dec-2015,4000,20

7839,KING,PRESIDENT,NULL,7698,17-Nov-2015,5000,10

7839,KING,PRESIDENT,NULL,7698,17-Nov-2015,6000,10

7844,TURNER,SALESMAN,7698,7788,8-Sep-2015,1500,30

7844,TURNER,SALESMAN,7698,7788,8-Sep-2015,2500,30

7876,ADAMS,CLERK,7788,7698,12-Jan-2015,1100,20

7876,ADAMS,CLERK,7788,7698,12-Jan-2015,2100,20

7900,JAMES,CLERK,7698,7566,3-Dec-2015,950,30

7900,JAMES,CLERK,7698,7566,3-Dec-2015,650,30

7902,FORD,ANALYST,7566,7782,3-Dec-2015,3000,20

7902,FORD,ANALYST,7566,7782,3-Dec-2015,2000,20

7934,MILLER,CLERK,7782,7782,23-Jan-2015,1300,10

7934,MILLER,CLERK,7782,7782,23-Jan-2015,300,10

];

COMMTable:

LOAD * INLINE

[

EMPNO2,COMM

7369,150

7499,300

7521,

7566,150

7654,1400

7698,150

7782,

7788,200

7839,200

7844,0

7876,200

7900,200

7902,200

7934,300

];

Re: Derived Quarter from DATE

MK_QSL — Wed, 01 Jul 2015 08:19:45 GMT

IF(DATE >= '01-JAN-2015' and DATE <= '06-APR-2015', 'Q1',

IF(DATE >= '07-APR-2015' and DATE <= '05-JUL-2015', 'Q2',

IF(DATE >= '06-JUL-2015' and DATE <= '10-OCT-2015', 'Q3',

IF(DATE >= '11-OCT-2015' and DATE <= '31-DEC-2015', 'Q4')))) as Quarter

Re: Derived Quarter from DATE

alexandros17 — Wed, 01 Jul 2015 08:26:52 GMT

Here it is

Re: Derived Quarter from DATE

qlikviewwizard — Wed, 01 Jul 2015 08:55:57 GMT

Thank you MRKachhiaIMP and Alexandros17

Re: Derived Quarter from DATE

Wed, 01 Jul 2015 09:10:03 GMT

Hi Qlikview Wizard,

try like this:

IF(DATE >= '01-JAN-2015' and DATE <= '06-APR-2015', 'Q1',

IF(DATE >= '07-APR-2015' and DATE <= '05-JUL-2015', 'Q2',

IF(DATE >= '06-JUL-2015' and DATE <= '10-OCT-2015', 'Q3',

IF(DATE >= '11-OCT-2015' and DATE <= '31-DEC-2015', 'Q4')))) as Quarter

Ramya.

Re: Derived Quarter from DATE

qlikviewwizard — Wed, 01 Jul 2015 10:35:15 GMT

Hi All,

If I have to years 2014 and 2015,Then how to calculate Quarter?

Please advise.

1-Jan-2014 to 6-Apr-2014 Then Q1

7-Apr-2014 to 5-Jul-2014 Then Q2

5-Jul-2014 to 10-Oct-2014 Then Q3

11-Oct 2014 to 3-Jan-2015 Then Q4

1-Jan-2015 to 6-Apr-2015 Then Q1

7-Apr-2015 to 5-Jul-2015 Then Q2

5-Jul-2015 to 10-Oct-2015 Then Q3

11-Oct 2015 to-3-Jan-2016 Then Q4

Script:

SALTable:

LOAD * INLINE [

EMPNO1,ENAME,JOB,INCHARGE1,INCHARGE2,DATE,SAL,DEPTNO

7369,SMITH,CLERK,7902,7566,17-Dec-2015,700,20

7369,SMITH,CLERK,7902,7566,17-Dec-2015,900,20

7499,ALLEN,SALESMAN,7698,7839,20-Feb-2015,1600,30

7499,ALLEN,SALESMAN,7698,7839,20-Feb-2015,1900,30

7521,WARD,SALESMAN,7698,7839,22-Feb-2015,0,30

7521,WARD,SALESMAN,7698,7839,22-Feb-2015,1650,30

7566,JONES,MANAGER,7839,7698,2-Apr-2015,2975,20

7566,JONES,MANAGER,7839,7698,2-Apr-2015,3975,20

7654,MARTIN,SALESMAN,7698,7788,28-Sep-2015,1250,30

7654,MARTIN,SALESMAN,7698,7788,28-Sep-2015,2250,30

7698,BLAKE,MANAGER,7839,7698,1-May-2015,2850,30

7698,BLAKE,MANAGER,7839,7698,1-May-2015,3850,30

7782,CLARK,MANAGER,7839,7566,9-Jun-2015,0,10

7782,CLARK,MANAGER,7839,7566,9-Jun-2015,3450,10

7788,SCOTT,ANALYST,7566,7782,9-Dec-2015,1250,20

7788,SCOTT,ANALYST,7566,7782,9-Dec-2015,4000,20

7839,KING,PRESIDENT,NULL,7698,17-Nov-2015,5000,10

7839,KING,PRESIDENT,NULL,7698,17-Nov-2015,6000,10

7844,TURNER,SALESMAN,7698,7788,8-Sep-2015,1500,30

7844,TURNER,SALESMAN,7698,7788,8-Sep-2015,2500,30

7876,ADAMS,CLERK,7788,7698,12-Jan-2015,1100,20

7876,ADAMS,CLERK,7788,7698,12-Jan-2015,2100,20

7900,JAMES,CLERK,7698,7566,3-Dec-2015,950,30

7900,JAMES,CLERK,7698,7566,3-Dec-2015,650,30

7902,FORD,ANALYST,7566,7782,3-Dec-2015,3000,20

7902,FORD,ANALYST,7566,7782,3-Dec-2015,2000,20

7934,MILLER,CLERK,7782,7782,23-Jan-2015,1300,10

7934,MILLER,CLERK,7782,7782,23-Jan-2015,300,10

7369,SMITH,CLERK,7902,7566,17-Dec-2014,700,20

7369,SMITH,CLERK,7902,7566,17-Dec-2014,900,20

7499,ALLEN,SALESMAN,7698,7839,20-Feb-2014,1600,30

7499,ALLEN,SALESMAN,7698,7839,20-Feb-2014,1900,30

7521,WARD,SALESMAN,7698,7839,22-Feb-2014,0,30

7521,WARD,SALESMAN,7698,7839,22-Feb-2014,1650,30

7566,JONES,MANAGER,7839,7698,2-Apr-2014,2975,20

7566,JONES,MANAGER,7839,7698,2-Apr-2014,3975,20

7654,MARTIN,SALESMAN,7698,7788,28-Sep-2014,1250,30

7654,MARTIN,SALESMAN,7698,7788,28-Sep-2014,2250,30

7698,BLAKE,MANAGER,7839,7698,1-May-2014,2850,30

7698,BLAKE,MANAGER,7839,7698,1-May-2014,3850,30

7782,CLARK,MANAGER,7839,7566,9-Jun-2014,0,10

7782,CLARK,MANAGER,7839,7566,9-Jun-2014,3450,10

7788,SCOTT,ANALYST,7566,7782,9-Dec-2014,1250,20

7788,SCOTT,ANALYST,7566,7782,9-Dec-2014,4000,20

7839,KING,PRESIDENT,NULL,7698,17-Nov-2014,5000,10

7839,KING,PRESIDENT,NULL,7698,17-Nov-2014,6000,10

7844,TURNER,SALESMAN,7698,7788,8-Sep-2014,1500,30

7844,TURNER,SALESMAN,7698,7788,8-Sep-2014,2500,30

7876,ADAMS,CLERK,7788,7698,12-Jan-2014,1100,20

7876,ADAMS,CLERK,7788,7698,12-Jan-2014,2100,20

7900,JAMES,CLERK,7698,7566,3-Dec-2014,950,30

7900,JAMES,CLERK,7698,7566,3-Dec-2014,650,30

7902,FORD,ANALYST,7566,7782,3-Dec-2014,3000,20

7902,FORD,ANALYST,7566,7782,3-Dec-2014,2000,20

7934,MILLER,CLERK,7782,7782,23-Jan-2014,1300,10

7934,MILLER,CLERK,7782,7782,23-Jan-2014,300,10

];

COMMTable:

LOAD * INLINE

[

EMPNO2,COMM

7369,150

7499,300

7521,

7566,150

7654,1400

7698,150

7782,

7788,200

7839,200

7844,0

7876,200

7900,200

7902,200

7934,300

];

Re: Derived Quarter from DATE

jagan — Thu, 02 Jul 2015 04:05:22 GMT

Hi,

Try like this

LOAD

IF(Month(DATE) <= 3 OR (Month(DATE) = 4 AND Day(DATE) <= 6), 'Q1',

IF(Month(DATE) <= 6 OR (Month(DATE) = 7 AND Day(DATE) <= 5), 'Q2',

IF(Month(DATE) <= 9 OR (Month(DATE) = 10 AND Day(DATE) <= 10), 'Q3',

IF(Month(DATE) <= 12 , 'Q4')))) AS Quarter

Regards,

Jagan.