Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
Master II
Master II

Derived Quarter from DATE

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

];

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

View solution in original post

6 Replies
MK_QSL
MVP
MVP

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

alexandros17
Partner - Champion III
Partner - Champion III

Here it is

qlikviewwizard
Master II
Master II
Author

Thank you MRKachhiaIMP and Alexandros17

Not applicable

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.

qlikviewwizard
Master II
Master II
Author

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

];

jagan
Luminary Alumni
Luminary Alumni

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.