Date and Quarter

[arulsettu]

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

[arulsettu]

Hi Rudolf

               i added two brackets due to error

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

i get only Q. did do any mistake plz correct if i am

thanks

[arulsettu]

it works Rudolf

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

but if i select march 21 it shows Q1. something wrong isn't it

plz clarify

thanks

[Anonymous]

=

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))

one bracket was missing, i tried it within my app, now I get all quarters

[arulsettu]

yes its working but if i select march 21 it shows as Q1. something wrong isn't it

plz clarify

[Anonymous]

where did you put the solution? in a listbox expression?

where did you select march 21?

you need to put in script so it is in the datamodel

canyou post your changed qvw

[arulsettu]

please find the app !

[Anonymous]

I suppose that due to the change of dateformat (from 21st on it is a new month), the date need to adjusted again

e.g. you select march and 21st, according to your changes this is feb 21st

so addmonths(youedate,1) resukts in march 21st

changing to addmonths(yourdate,2) should do it

but I did not check if out completely, but give it a try and test the results

[arulsettu]

Hi Rudolf,

     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,

     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

but getting same result for march 21 as Q1

plz help

thanks

[#10]

Hi Arul,

Can you try following,

='Q' & ceil(num(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))))/ 3)

Regards,

Faisal

[arulsettu]

if you need i will upload the qvd also

can you tell me what is the issue?

thanks