Month Name for fiscal year

[helen_pip]

Hello

I have a problem trying to obtain the month name of the financial year from my script

April is the start of my fiscal year so I have set the following:

SET

vFirstMonth = 4

In my script I am able to ascertain the Fiscal Year

which returns 2012/2013

                           2013/2014

num(month(addmonths(REFERRAL_DATE, 1-$(vFirstMonth))))AS DateFieldFiscalMonth,

Which returns 1 - 12 ith 1 being April

However, I can seem to get the month name to show correctly, and by adjusting the code below to get month name, my code then assumes that 1 = Jan and 2 = Feb instead of 1=Apr:

month(addmonths(REFERRAL_DATE, 1-$(vFirstMonth))) AS DateFieldFiscalMonthName   

How can I achieve the month name where April will still be month 1?

Does anyone have any ideas?

Thanks

Helen

[Jason_Michaelides]

Hi Helen,

Not really sure I follow....are you passing in a date and trying to get back:

1. Fiscal year (assuming 1st April year-start)
2. Month name of date

If so, for the fiscal year consider using YearStart():

Year(YearStart(YourDateField,0,4))

For the month name can't you just use Month()?

Hope this helps,

Jason

[helen_pip]

Hello

Oh yes....much simpler and works lovely

However I have put the 2 together as

Month(YourDateField) &-&

Year(YearStart(YourDateField,0,4))   to get MonthYear

However when I put MOnthYear into a graph, it puts Apr-2012 and Apr-2013 next to each other

Do you know how I could sort the graph.  If I sort by YearStart I then get Apr-2012 , Aug 2012 

Could you kindly advise?

Thanks

Helen

[Jason_Michaelides]

It's because you are creating a text field so it is being sorted alphabetically. You need to convert it back into a date/number.  Several ways of doing this, I think I would probably use Dual() in this situations:

Dual(

     Month(YourDateField) & '-' & Year(YearStart(YourDateField,0,4))

     ,Num#(Year(YearStart(YourDateField,0,4)) & '.' & Month(YourDateField)

     )

Try that and see if it works. You could also use MakeDate():

MakeDate(Year(YearStart(YourDateField,0,4)),Month(YourDateField))

This one might be easier actually.

Jason