topic Date and Quarter in QlikView

Date and Quarter

arulsettu — Tue, 31 Mar 2015 06:44:06 GMT

Hi,

I am having a date field like this 426566. so i used below code

Date(TD_DOC_DT,'DD/MM/YYYY') as Trans_date

now i need month and quarter like

01/01 to 20/01 - jan

21/01 to 20/02 - feb q1

21/02 to 20/03 - mar

21/03 to 20/04 - apr

21/04 to 20/05 - may q2

21/05 to 20/06 - jun

21/06 to 20/07 - jul

21/07 to 20/08 - aug q3

21/08 to 20/09 - sep

21/09 to 20/10 - oct

21/10 to 20/11 - nov q4

21/11 to 31/12 - dec

i tried like this

if(daymonth>='1/Jan' and daymonth<='20/Jan','Jan',

if(daymonth>='21/Jan' and daymonth<='20/Feb','Feb')) as Trans_detail_Month

plz suggest some ideas

thanks

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 06:55:33 GMT

any help guys?

Re: Date and Quarter

Anonymous — Tue, 31 Mar 2015 06:57:44 GMT

just format as

date('31.03.2015','DD/MM/YYYY-MMM') you get 31/03/2015-Mrz

fpr Quarter use ceil functtion

'Q' &

ceil(month(TempDate

) / 3))

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 07:00:32 GMT

Hi Rudlof,

Format is not a issue. base on the date filed i need to create month like this

01/01 to 20/01 - jan

21/01 to 20/02 - feb

21/02 to 20/03 - mar

21/03 to 20/04 - apr

21/04 to 20/05 - may

21/05 to 20/06 - jun

21/06 to 20/07 - jul

21/07 to 20/08 - aug

21/08 to 20/09 - sep

21/09 to 20/10 - oct

21/10 to 20/11 - nov

21/11 to 31/12 - dec

thanks

Re: Date and Quarter

Tue, 31 Mar 2015 07:01:37 GMT

Hi,

Try like this,

month(Date(TD_DOC_DT,'DD/MM/YYYY')) as Month

if(num(month(Date(TD_DOC_DT,'DD/MM/YYYY')))>=12 and num(month(Date(TD_DOC_DT,'DD/MM/YYYY')))<=2,'Q1',

if(num(month(Date(TD_DOC_DT,'DD/MM/YYYY')))>=3 and num(month(Date(TD_DOC_DT,'DD/MM/YYYY')))<=5,'Q2',

if(num(month(Date(TD_DOC_DT,'DD/MM/YYYY')))>=6 and num(month(Date(TD_DOC_DT,'DD/MM/YYYY')))<=8,'Q3',

if(num(month(Date(TD_DOC_DT,'DD/MM/YYYY')))>=9 and num(month(Date(TD_DOC_DT,'DD/MM/YYYY')))<=11,'Q3')))) as FinancialQuarter,

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 07:04:05 GMT

Hi Harshal,

thanks for reply. how to create month like above

Re: Date and Quarter

Anonymous — Tue, 31 Mar 2015 07:08:00 GMT

if day(yourdate)<21 or (day(yourdate)>=21 and month(yourdate)='dec'), month(yourdate), month(addmonth(yourdate),1)) delivers just jan, feb, ... dec

try it, should work

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 07:16:24 GMT

i dint get you can you please check this one

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 07:28:35 GMT

its giving error in addmonths part

Re: Date and Quarter

Anonymous — Tue, 31 Mar 2015 07:30:03 GMT

sorry, my mistake assmonths instead of addmonth

if (day(yourdate)<21 or (day(yourdate)>=21 and month(yourdate)='dec') , month(yourdate), month(addmonths(yourdate,1))) as

monthofyourdate

Re: Date and Quarter

Anonymous — Tue, 31 Mar 2015 07:33:13 GMT

goto list box properties and select expression and insert this expression

if (day(TD_DOC_DT)<21 or (day(TD_DOC_DT)>=21 and month(TD_DOC_DT)='dec') , month(TD_DOC_DT), month(addmonths(TD_DOC_DT,1)))

it works with your app

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 07:34:45 GMT

Hi Rudolf,

thanks for replying. to if select the jan month it showing all 31 days. and for dec showing 30 days.

is there any mistake i made plz correct me

thanks

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 07:41:31 GMT

i will check it thank you. can you explain the above code for understanding

Re: Date and Quarter

Tue, 31 Mar 2015 07:47:36 GMT

Hi,

You can try this logic.

Date(Date#(Date_field, num(Date_field)),'MM/YY') as Trans_date

'Q' & Ceil([DAy_month]/3, 1) as Quarter,

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 07:48:54 GMT

hi Rudolf,

how to derive the quarter from this month filed

Re: Date and Quarter

Anonymous — Tue, 31 Mar 2015 07:53:33 GMT

'Q' &

ceil(month(yourmonthfield) / 3)) AS Quarter,

Re: Date and Quarter

Tue, 31 Mar 2015 07:53:52 GMT

Hi,

You can try this.

'Q' & Ceil([DAY_month]/3, 1) as Quarter,

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 07:58:25 GMT

but i need

01/01 to 20/01 - jan

21/01 to 20/02 - feb as Q1

21/02 to 20/03 - mar

Re: Date and Quarter

arulsettu — Tue, 31 Mar 2015 08:02:16 GMT

i did this

if (day(TD_DOC_DT)<21 or (day(TD_DOC_DT)>=21 and month(TD_DOC_DT)='dec') , month(TD_DOC_DT), month(addmonths(TD_DOC_DT,1))) as monthcheck

quarter

=ceil(Month(monthcheck)/3)

it gives 1,4 only

Re: Date and Quarter

Anonymous — Tue, 31 Mar 2015 08:08:41 GMT

if (day(TD_DOC_DT)<21 or (day(TD_DOC_DT)>=21 and month(TD_DOC_DT)='dec') , 'Q' & ceil(month(TD_DOC_DT)/3, 'Q' & ceil(month(addmonths(TD_DOC_DT,1))/3) as quartercheck

monthcheck only contains the monthnames, you need another field based on your requrements (specal 21 of march should show as Q2)