topic Re: Month Name for fiscal year in QlikView

Month Name for fiscal year

helen_pip — Fri, 10 May 2013 10:13:05 GMT

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

Re: Month Name for fiscal year

Jason_Michaelides — Fri, 10 May 2013 10:21:51 GMT

Hi Helen,

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

Fiscal year (assuming 1st April year-start)
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

Re: Month Name for fiscal year

helen_pip — Fri, 10 May 2013 10:30:25 GMT

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

Re: Month Name for fiscal year

Jason_Michaelides — Fri, 10 May 2013 10:37:23 GMT

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