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

[Anonymous · Accepted solution]

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

[arulsettu]

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

[arulsettu]

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

[#5]

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,

[arulsettu]

hi Rudolf,

               how to derive the quarter from this month filed

[Anonymous]

'Q' &

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

[#8]

Hi,

You can try this.

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

[arulsettu]

but i need

      01/01 to 20/01 - jan

      21/01 to 20/02 - feb     as Q1

      21/02 to 20/03 - mar  

[arulsettu]

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

[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) as quartercheck

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