month and quarter problem

[arulsettu]

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

[arulsettu]

any help guys

its really emergency

thanks

[MK_QSL]

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

];

[arulsettu]

Hi manish,  

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

thanks !

[MK_QSL]

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

[Anonymous]

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

[arulsettu]

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

[arulsettu]

hi Rudalf

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

thanks

[arulsettu]

Rudolf and Manish,

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

thanks

[arulsettu]

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

thanks