Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
Master II
Master II
‎2015-07-01 04:10 AM

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


];
EMP.qvw
1,038 Views
1 Solution

Accepted Solutions
MK_QSL
MVP
MVP
‎2015-07-01 04:19 AM

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

784 Views
6 Replies
MK_QSL
MVP
MVP
‎2015-07-01 04:19 AM

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

785 Views
alexandros17
Partner - Champion III
Partner - Champion III
‎2015-07-01 04:26 AM

Here it is

EMP.qvw
784 Views
qlikviewwizard
Master II
Master II
‎2015-07-01 04:55 AM
Author

In response to alexandros17

Thank you MRKachhiaIMP and Alexandros17

784 Views
0 Likes
Not applicable
‎2015-07-01 05:10 AM

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.

784 Views
qlikviewwizard
Master II
Master II
‎2015-07-01 06:35 AM
Author

In response to

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


];
784 Views
0 Likes
jagan
Luminary Alumni
Luminary Alumni
‎2015-07-02 12:05 AM

In response to qlikviewwizard

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.

784 Views