Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to display financial month-year formate of my date field.my financila year is Apr-Mar.
i am using following script,
date(monthstart(Date field name), 'MMM-YYYY') AS MonthYear
but it is displaying 'Apr-2012' but i want to display 'Apr-2013' when clicking on the Apr month.i used lot of syntax but it is not correct.
can any one have idea...
regards,
baru
Or maybe:
Date(MonthStart(IF(Month(Date)>3,AddMonths(Date,12),Date)),'MMM-YYYY') AS MonthYear
Hope this helps,
Jason
HI,
Try this
Mast_Cal:
Load
Date(yearstart(TempDate,0,4),'YYYY') as FIN_YEAR,
if(num(month(TempDate))<4,num(Month(TempDate))+9,num(Month(TempDate))-3) as FIN_MONTH_NUM,
'Q'& Ceil(if(num(month(TempDate))<4,num(Month(TempDate))+9,num(Month(TempDate))-3)/3) as FIN_QTR,
Month(TempDate) as FIN_MONTH
From XYZ;
Regards,
Kaushik Solanki
Or maybe:
Date(MonthStart(IF(Month(Date)>3,AddMonths(Date,12),Date)),'MMM-YYYY') AS MonthYear
Hope this helps,
Jason
Hi Kaushik,
Thanks for your reply,
I already converted month and year to fin_month and fin_year.
now my requirement is to show fin_monthyear,when i am selecting Apr,2013(i.e.,this Apr)
it will display Apr-2013,now it is displaying Apr-2012.
have any idea.
Regards,
Baru
HI,
One thing is you concat the Month and Financial Year like
Month & ' - ' Fin_Year as MonthYear
Second is you create Month Year as
MonthName(Addmonths(Date,+12)) as Fin_Month_Year
Cause Financial Year for Apr 2011 - Mar 2012 will be 2012.
If you call Year Apr2011- Mar2012 as Financial Year 2011 then use this
MonthName(Date) as Fin_Month_Year
Regards,
Kaushik Solanki
Hi,
Thankyou Kaushik and Jason for your solutions.
Regards,
Baru
Hi Baru,
Its our pleasure.
Regards,
Kaushik Solanki