Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
View solution in original post
any help guys?
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))
Hi Rudlof,
Format is not a issue. base on the date filed i need to create month like this
21/01 to 20/02 - feb
21/04 to 20/05 - may
21/07 to 20/08 - aug
21/10 to 20/11 - nov
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,
Hi Harshal,
thanks for reply. how to create month like above
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
i dint get you can you please check this one
its giving error in addmonths part
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
