Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
Hi
you can use this
YearName(DATE,0,4) as [FinancialYear]
eg.
YearName(Today(),0,4) it will give 2009-2010Rahul
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.
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;
That just one of the possible solutions
Rds,
AT
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
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
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
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
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