Deriving Quarter from date

[qlikviewwizard]

Hi All,

I have Date data in excel file in YYYYMMDD format.

Used this function to convert to date format -

Date#('20131230','YYYYMMDD') as Date

I derived Quarter with the below code:

'Q' & Ceil(Month(Date)/3) as Quarter,

But I am getting Q1,Q2,Q3,Q4,Q.

I don't want Q. How to elimate this?

Why it is showing Q even I don't have any blank rows in excel file?

[arulsettu]

hi try like this

date(Date#('20131230','YYYYMMDD'),'DD/MM/YYYY') as date,

'Q' & Ceil(Month(Date)/3) as Quarter

[qlikviewwizard]

Hi arulsettu, Tried. But not working.

[arulsettu]

can u post your qvw

[anbu1984]

Maybe you don't have proper date. Can you post your excel file

[qlikviewwizard]

This is the data:

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	LASTDATE	MODIFYDATE
EMPNo1	ENAME1	CLERK	EMPNo11	19830112	1100		20	20150302	20140101
EMPNo2	ENAME2	SALESMAN	EMPNo3	19810220	1600	300	30	20150303	20140202
EMPNo3	ENAME3	MANAGER	ENAME8	19810501	2850		30		20140303
EMPNo4	ENAME4	MANAGER	ENAME8	19810609	2450		10		20140404
EMPNo5	ENAME5	ANALYST	ENAME7	19811203	3000		20	20150306	20140505
EMPNo6	ENAME6	CLERK	EMPNo3	19811203	950		30	20150307	20140606
EMPNo7	ENAME7	MANAGER	ENAME8	19810402	2975		20		20140707
EMPNo8	ENAME8	PRESIDENT	NULL	19811117	5000		10		20140808
EMPNo9	ENAME9	SALESMAN	EMPNo3	19810928	1250	1400	30	20150310	20140909
EMPNo10	ENAME10	CLERK	EMPNo4	19820123	1300		10	20150311	20141010
EMPNo11	ENAME11	ANALYST	ENAME7	19821209	3000		20	20141111	20141111
EMPNo12	ENAME12	CLERK	EMPNo5	19801217	800	NULL	20	20150313	20141212
EMPNo13	ENAME13	SALESMAN	EMPNo3	19810908	1500	0	30	20150314	20140113
EMPNo14	ENAME14	SALESMAN	EMPNo3	19810222	1250	500	30	20150315	20140214
EMPNo15	ENAME15	SALESMAN	EMPNo5	19801217	800		20		20140315
EMPNo16	ENAME16	CLERK	EMPNo6	19810908	1500	300	30	20150317	20140416
EMPNo17	ENAME17	ANALYST	ENAME8	19810222	1250		30	20140517	20140517
EMPNo18	ENAME18	CLERK	EMPNo5	19801217	800		10	20150319	20140618
EMPNo19	ENAME19	SALESMAN	EMPNo3	19810908	1500		20	20150320	20140719
EMPNo20	ENAME20	SALESMAN	EMPNo3	19810222	1250		30	20150321	20140820
EMPNo21	ENAME21	SALESMAN	EMPNo7	19801217	800		20	20150322	20140921
EMPNo22	ENAME22	CLERK	EMPNo8	19810908	1500		10	20150323	20141022
EMPNo23	ENAME23	ANALYST	ENAME9	19810222	1250	1400	30	20150324	20141123
EMPNo24	ENAME24	CLERK	EMPNo5	19801217	800		10		20141224
EMPNo25	ENAME25	SALESMAN	EMPNo3	19810908	1500		20	20150303	20140125
EMPNo26	ENAME26	SALESMAN	EMPNo3	19810222	1250	50	20	20150304	20140226
EMPNo27	ENAME27	SALESMAN	EMPNo9	19801217	800	0	30	20150305	20140327
EMPNo28	ENAME28	CLERK	EMPNo10	19810908	1500	500	30	20150306	20140428
EMPNo29	ENAME29	ANALYST	ENAME10	19810222	1250		20	20150307	20140501
EMPNo30	ENAME30	CLERK	EMPNo5	19801217	800	300	30	20150308	20140602
EMPNo31	ENAME31	SALESMAN	EMPNo3	19810908	1500		30	20150309	20140703
EMPNo32	ENAME32	SALESMAN	EMPNo3	19810222	1250		10	20150310	20140804
EMPNo33	ENAME33	SALESMAN	EMPNo11	19801217	800		20		20140905
EMPNo34	ENAME34	CLERK	EMPNo12	19810908	1500		30	20150312	20141006
EMPNo35	ENAME35	ANALYST	ENAME11	19810222	1250		20	20150313	20141107
EMPNo36	ENAME36	CLERK	EMPNo5	19801217	800		10	20150314	20141208
EMPNo37	ENAME37	SALESMAN	EMPNo3	19810908	1500	1400	30	20150315	20140109
EMPNo38	ENAME38	SALESMAN	EMPNo3	19810222	1250		10	20150316	20140210
EMPNo39	ENAME39	SALESMAN	EMPNo13	19801217	800		20		20140311
EMPNo40	ENAME40	CLERK	EMPNo14	19810908	1500	50	20	20150318	20140412
EMPNo41	ENAME41	ANALYST	ENAME12	19810222	1250	0	30	20140513	20140513
EMPNo42	ENAME42	CLERK	EMPNo5	19801217	800	500	30	20140614	20140614
EMPNo43	ENAME43	SALESMAN	EMPNo3	19810908	1500		20	20150321	20140715
EMPNo44	ENAME44	SALESMAN	EMPNo3	19810222	1250	300	30	20150322	20140816
EMPNo45	ENAME45	SALESMAN	EMPNo15	19801217	800		30	20150323	20140917
EMPNo46	ENAME46	CLERK	EMPNo16	19810908	1500		10	20141018	20141018
EMPNo47	ENAME47	ANALYST	ENAME13	19810222	1250		20		20141119
EMPNo48	ENAME48	CLERK	EMPNo5	19801217	800		30	20150303	20141220
EMPNo49	ENAME49	SALESMAN	EMPNo3	19810908	1500		20	20150304	20141121
EMPNo50	ENAME50	SALESMAN	EMPNo3	19810222	1250		10	20150305	20141222
EMPNo51	ENAME51	SALESMAN	EMPNo17	19801217	800	1400	30		20150123
EMPNo52	ENAME52	CLERK	EMPNo18	19810908	1500		10		20150224
EMPNo53	ENAME53	ANALYST	ENAME14	19810222	1250		20	20150308	20150325
EMPNo54	ENAME54	CLERK	EMPNo5	19801217	800	50	20	20150309	20150426
EMPNo55	ENAME55	SALESMAN	EMPNo3	19810908	1500	0	30	20150310	20150527
EMPNo56	ENAME56	SALESMAN	EMPNo3	19810222	1250	500	30	20150311	20150628
EMPNo57	ENAME57	SALESMAN	EMPNo19	19801217	800		20	20150312	20150701
EMPNo58	ENAME58	CLERK	EMPNo20	19810908	1500	300	30	20150313	20150802
EMPNo59	ENAME59	ANALYST	ENAME15	19810222	1250		30	20150314	20150903
EMPNo60	ENAME60	CLERK	EMPNo5	19801217	800		10	20151004	20151004
EMPNo61	ENAME61	SALESMAN	EMPNo3	19810908	1500		20	20150316	20151105
EMPNo62	ENAME62	SALESMAN	EMPNo3	19810222	1250		30	20150317	20151206
EMPNo63	ENAME63	SALESMAN	EMPNo21	19801217	800		20	20150318	20150107
EMPNo64	ENAME64	CLERK	EMPNo22	19810908	1500		10	20150319	20150208
EMPNo65	ENAME65	ANALYST	ENAME16	19810222	1250	1400	30	20150320	20150309
EMPNo66	ENAME66	CLERK	EMPNo5	19801217	800		10	20150321	20150410
EMPNo67	ENAME67	SALESMAN	EMPNo3	19810908	1500		20	20150322	20150511
EMPNo68	ENAME68	SALESMAN	EMPNo3	19810222	1250	50	20	20150323	20150612
EMPNo69	ENAME69	SALESMAN	EMPNo23	19801217	800	0	30		20150713
EMPNo70	ENAME70	CLERK	EMPNo24	19810908	1500	500	30	20150302	20150814
EMPNo71	ENAME71	ANALYST	ENAME17	19810222	1250		20	20150303	20150915
EMPNo72	ENAME72	CLERK	EMPNo5	19801217	800	300	30	20150304	20151016
EMPNo73	ENAME73	SALESMAN	EMPNo3	19810908	1500		30	20150305	20151117
EMPNo74	ENAME74	SALESMAN	EMPNo3	19810222	1250		10	20150306	20151218
EMPNo75	ENAME75	SALESMAN	EMPNo25	19801217	800		20	20150307	20150119
EMPNo76	ENAME76	CLERK	EMPNo26	19810908	1500		30		20150220
EMPNo77	ENAME77	ANALYST	ENAME18	19810222	1250		20	20150309	20150321
EMPNo78	ENAME78	CLERK	EMPNo5	19801217	800		10	20150310	20150422
EMPNo79	ENAME79	SALESMAN	EMPNo3	19810908	1500	1400	30	20150311	20150523
EMPNo80	ENAME80	SALESMAN	EMPNo3	19810222	1250		10	20150312	20150624
EMPNo81	ENAME81	SALESMAN	EMPNo27	19801217	800		20	20150313	20150725
EMPNo82	ENAME82	CLERK	EMPNo28	19810908	1500	50	20	20150314	20150826
EMPNo83	ENAME83	ANALYST	ENAME19	19810222	1250	0	30	20150315	20150927
EMPNo84	ENAME84	CLERK	EMPNo5	19801217	800	500	30	20150316	20151028
EMPNo85	ENAME85	SALESMAN	EMPNo3	19810908	1500		20	20151101	20151101
EMPNo86	ENAME86	SALESMAN	EMPNo3	19810222	1250	300	30	20151202	20151202
EMPNo87	ENAME87	SALESMAN	EMPNo29	19801217	800		30	20150319	20150103
EMPNo88	ENAME88	CLERK	EMPNo30	19810908	1500		10	20150320	20150204
EMPNo89	ENAME89	ANALYST	ENAME20	19810222	1250		20	20150321	20150305
EMPNo90	ENAME90	CLERK	EMPNo5	19801217	800		30	20150322	20150406
EMPNo91	ENAME91	SALESMAN	EMPNo3	19810908	1500		20	20150323	20150507
EMPNo92	ENAME92	SALESMAN	EMPNo3	19810222	1250		10	20150317	20150608
EMPNo93	ENAME93	SALESMAN	EMPNo31	19801217	800	1400	30	20150318	20150709
EMPNo94	ENAME94	CLERK	EMPNo32	19810908	1500		10	20150319	20150810
EMPNo95	ENAME95	ANALYST	ENAME21	19810222	1250		20	20150320	20150911
EMPNo96	ENAME96	CLERK	EMPNo5	19801217	1250	50	20	20150321	20151012
EMPNo97	ENAME97	SALESMAN	EMPNo3	19810908	800	0	30	20150322	20151113
EMPNo98	ENAME98	SALESMAN	EMPNo3	19810222	1500	500	30	20150323	20151214
EMPNo99	ENAME99	SALESMAN	EMPNo33	19801217	1250	1400	20	20150317	20151115
EMPNo100	ENAME100	SALESMAN	EMPNo33	19801217	1250	1400	20	20150317	20151216

[arulsettu]

check this

[anbu1984]

You have blanks in Last date

[jagan · Accepted solution]

Hi,

You have some Null values in LastDate date column so use this expression

1. If(Len(Trim(Date)) > 0, 'Q' & Ceil(Month(Date)/3)) as Quarter, 
2. 
   

Hope this helps you.

Regards,

jagan.