financial year

[#1]

I have a year field which holds the value '2011'

Is there a way I can change this to 2010-2011 so it reflects the financial year?

Currently I have:   yearname(YEAR, 0, 4) as FinancialYear

This is giving me the value '1905-1906' for some reason.

[swuehl]

brianm10,

if your financial year starts in April, you will need to give at least month information, too, otherwise QV can't tell if year 2011 belongs to 2010-2011 or 2011-2012, right?

You need to use something like

yearname(DATE,0,4)

Please not the use of a DATE with its numerical representation here, instead of YEAR.

Do you have a DATE field available, too?

Hope this helps,

Stefan

[#3]

Thanks for the reply.

I have a month field but its in the format as 201110.

Will I need to change this also?

[#4]

When I try and convert this field with the following code:

date(posting_date, 'MM-YYYY') as Date

This gives me the values:

Dec-1899

Apr-56962

Apr-56592

Apr-56592

[swuehl]

posting_date is this YearMonth field you just mentioned? It seems it has no underlying numerical date representation, how is posting_date created? Read from a database or have you used a QV function on a DATE field?

Well, if posting_date is just a YearMonth value, you could create a DATE field based on that, e.g. in the script like:

LOAD

...

makedate(right(posting_date,4),left(posting_date,2),1) as MonthStartDate,

...

FROM ...

and then use this MonthStartDate as input to yearname, maybe also in the script

LOAD

...

makedate(right(posting_date,4),left(posting_date,2),1) as MonthStartDate,

yearname(makedate(right(posting_date,4),left(posting_date,2),1),0,4) as YearName,

...

FROM ...

Hope this helps,

Stefan

[#6]

Its read from a flat file, basically its just test data.

Currently the only values in this field are '20111101', '20111102', '20111103'

So its a concatenation of year/month/day

Thanks for your help

[swuehl · Accepted solution]

I see,

these values probably don't match your standard DateFormat set in the script or OS.

You could read this in as

...

Date(Date#(FIELD,'YYYYMMDD') as YourDateField,

...

Hope this helps,

Stefan

[#8]

Thanks, that peice of script fixed the issue