How to load the financial year during the load script itself?

[#1]

Hi All,

How to load the financial year during the load script in the same way which has been given below????

Calender:
 Load
 DATE,
 Day(DATE) as Day,
 DayName(DATE) as [Day Name],
 WeekDay(DATE) as [Day of Week],
 WeekName(DATE) as [Week Name],
 LunarWeekName(DATE) as [Lunar Week Name],
 Month(DATE) as [Month],
 MonthName(DATE) as [Month Name],
 QuarterName(DATE) as [Quarter Name],
 Year(DATE) as [Year]
 ????(DATE) as [FinancialYear]

Resident t1;

Drop Table t1;

[#2]

Hi

you can use this

YearName(DATE,0,4) as [FinancialYear]

eg.

YearName(Today(),0,4) it will give 2009-2010

Rahul

[Oleg_Troyansky]

We usually load the table of fiscal periods with the beginning and end dates, and then generate the calendar for all the days the belong to each period, using WHILE. This way, each date is getting associated with the fiscal Year and Period number.

[Anonymous]

HI, dsjain,

If my memory doesn't fail, then the begining and the end of the financial year could be different from calendar year. I have similar case with quarters what are different from quarters based on months. I use the source table with period ending dates and Lookup function.

In your case may be source table in Excel and Intervalmatch statement could be the solution

E.g. the source table could look like...

Finansial year Begin Date End Date
2006 2006.02.01 2007.01.31
2007 2007.02.01 2008.01.31
2008 2008.02.01 2009.01.31
2009 2009.02.01 2010.01.31
2010 2010.02.01 2011.01.31

...and the statement

FinYears:
Load
 [Financial Year] as FinYear,
 Num(Date([Begin Date])) as FinBegin,
 Num(Date([End Date])) as FinEnd
From SourceExcel.xls........;

Left Join (Calender)
Intervalmatch (DATE, FinYear)
Load
 FinBegin,
 FinEnd,
 FinYear
Resident FinYears;

After that you can join table FinYears to Calender and drop unnecessary table and fields.

That just one of the possible solutions

Rds,
AT

[#5]

If you are looking for a function, like all the other functions in you example, that returns the FinancialYear.. I'm afraid you won't find it.

A FinancialYear does not have a standard function, because there is no default definition for it. The definition for FinancialYear is something you will have to make yourself, because it can be different for each company.

Your Financial Year could start on February 2nd, or October 21st, or any other date..

Three possible options: (there are more)

- If your Financial year is the same as the Calendar Year, you can just use Year()

- If it is not too complex, you might think of a calculation, for example: if your financial year begins in March, then you could do something like

if( Month(DATE)<=3,
  Year(DATE) -1,
  Year(DATE),
) as [FinancialYear]

- If the definition is complex, you should make a seperate table (import or inline) that you can then join/map to you existing calender, like already suggested by Artjoms Tukums

[#6]

Hi Rahul!

Thanks for your reply! Instead of using the script sent by you I am using another script and getting the required result. May i know will it affects some time later on.

YearName(DATE,0,4) as [FinancialYear1]

// Instead of using the above one. I am using the below one

Date(yearstart(DATE,0,4),'YYYY') as [Fiscal Year]

Thanks and Regards,

Rikab

[#7]

Hi,

Both will give the same result

only difference is in out put

YearName(DATE,0,4) will give 2009-2010

Date(yearstart(DATE,0,4),'YYYY') will give 2009

other wise both are correct for any given date

Rahul

[#8 · Accepted solution]

Hi At,

Using the below function I am getting the required result. Please check and let me know whether I am correct or not. If I am wrong let me know where I am wrong.

Date(yearstart(DATE,0,4),'YYYY') as [Fiscal Year] // Gives output as 2005,2006
YearName(DATE,0,4) as [FinancialYear1] // Gives output as 2005-2006,2006-2007

Thanks and Regards,

Rikab

[#9]

Hi MdBoer,

I got it done by using the below function. As for your clients financial year starts from april 1st and not in the middle like that way you have mentioned in your example. Please refer and put me your suggestions.

<pre>Date(yearstart(DATE,0,4),'YYYY') as [Fiscal Year] // Gives output as 2005,2006
YearName(DATE,0,4) as [FinancialYear1] // Gives output as 2005-2006,2006-2007

Thanks and Regards,

Rikab