topic Re: month and quarter problem in QlikView

month and quarter problem

arulsettu — Tue, 31 Mar 2015 10:02:50 GMT

Hi based on my date field i need to create month and quarter

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

need to create quarter based on above month

as per HRLinder

big help for month 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,2))) as monthcheck

and for quarter

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,2))/3)) as quartercheck

if i select 21st march it should show Q2. but it showing Q1.

so anyone can help me out

thanks

Re: month and quarter problem

arulsettu — Tue, 31 Mar 2015 10:09:59 GMT

any help guys

its really emergency

thanks

Re: month and quarter problem

MK_QSL — Tue, 31 Mar 2015 10:29:06 GMT

Test:

Load

IF(DAY([FROM DATE]) <= 20, Month([FROM DATE]), Month([TO DATE])) as Month,

'Q' & CEIL(IF(DAY([FROM DATE]) <= 20, Month([FROM DATE]), Month([TO DATE]))/3) as Quarter;

Load

[FROMTO DATE],

Date#(Left([FROMTO DATE],5)&'/'&Year(Today()),'DD/MM/YYYY') as [FROM DATE],

Date#(Right([FROMTO DATE],5)&'/'&Year(Today()),'DD/MM/YYYY') as [TO DATE]

Inline

[

FROMTO DATE

01/01 to 20/01

21/01 to 20/02

21/02 to 20/03

21/03 to 20/04

21/04 to 20/05

21/05 to 20/06

21/06 to 20/07

21/07 to 20/08

21/08 to 20/09

21/09 to 20/10

21/10 to 20/11

21/11 to 31/12

];

Re: month and quarter problem

arulsettu — Tue, 31 Mar 2015 10:38:53 GMT

Hi manish,

i am getting empty month and quarter fields. please check this sample app

thanks !

Re: month and quarter problem

MK_QSL — Tue, 31 Mar 2015 10:52:48 GMT

Arul,.... You are asking something different and testing on something different...

Request you to check the format of your TD_DOC_DT... which is M/D/YYYY and you have asked 21/01 to 20/02????

How can we proceed?

Kind request you to provide data similar to your data model which will help you to get quick answer...

Please let me know how you want me to help you by some detailed explanation...

Thanks for your understanding...

Re: month and quarter problem

Anonymous — Tue, 31 Mar 2015 10:53:03 GMT

i testet with your app. I inserted a table box with al fields of your table

I define a textbox

if (day(TD_DOC_DT)<21 , 'Q' & ceil(month(TD_DOC_DT)/3), 'Q' & ceil(month(addmonths(TD_DOC_DT,2))/3))

I select 3/24/2014 inthe table box and the textbox shows Q2

I tried also with a straight table and it shows Q2 wjhen i define the above expression as a dimension

Re: month and quarter problem

arulsettu — Tue, 31 Mar 2015 11:02:41 GMT

Hi manish,

your right TD_DOC_DT my date field. my requirement is i need to create financial month. here year is common.

i need to create below month from TD_DOC_DT. and quarter also. if i select march 21st it should show Q2.

01/01 to 20/01

21/01 to 20/02

21/02 to 20/03

21/03 to 20/04

21/04 to 20/05

21/05 to 20/06

21/06 to 20/07

21/07 to 20/08

21/08 to 20/09

21/09 to 20/10

21/10 to 20/11

21/11 to 31/12

thank you and that is the original data. if anything need let me know please

Re: month and quarter problem

arulsettu — Tue, 31 Mar 2015 11:04:11 GMT

hi Rudalf

can you please post the app you did with month and quarter

thanks

Re: month and quarter problem

arulsettu — Tue, 31 Mar 2015 11:14:40 GMT

Rudolf and Manish,

Please check this app. it has the correct data. and help me

thanks

Re: month and quarter problem

arulsettu — Tue, 31 Mar 2015 11:22:11 GMT

now it showing my date format like 36162. can you guys help me please

thanks

Re: month and quarter problem

arulsettu — Tue, 31 Mar 2015 11:38:17 GMT

any help guys

Re: month and quarter problem

Anonymous — Tue, 31 Mar 2015 11:49:19 GMT

just write date(yourdatefield)

look at qvw, just reload (saved without data to save space)

Re: month and quarter problem

arulsettu — Tue, 31 Mar 2015 11:53:27 GMT

Hi Rudolf,

i did this

date(TD_DOC_DT) as TD_DOC_DT,

day(TD_DOC_DT) as TD_day,

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,2))) as monthcheck,

if (day(TD_DOC_DT)<21 , 'Q' & ceil(month(TD_DOC_DT)/3), 'Q' & ceil(month(addmonths(TD_DOC_DT,2))/3)) as quarter,

but no use. please correct me if i did any mistake

thanks

Re: month and quarter problem

arulsettu — Tue, 31 Mar 2015 12:58:57 GMT

i will check and tell u

thanks