Fiscal Week and year from Timestamp??

[Madhavan VM]

Fiscal week starts from first week of February. hence 1st Jan 2006 would be of fiscal week 49 and fiscal year 2005.
Week 1 starts from 1st of January.

I have timestamp as an input field. Here if I am trying to get week from timestamp then what I am getting is week of the year.

I have used the below funtion for the above conversion:

'W': YearweekFromDate(TimestampToDate(time_stamp_field))
'Y': YearFromDate(TimestampToDate(time_stamp_field))

For this if the input is 1st Jan 2006 then the results are W1 and Y2006.

Is there a function which will give me the fiscal year and week from timestamp?

Appreciate your response regarding this.

thanks and regards,
Ajith

[sud]

Since fiscal years vary from country to country, there is no built in function. But it is fairly easy to calculate. For example,

Code: Select all

Y: if MonthFromDate(TimestampToDate(time_stamp_field)) < 2 then YearFromDate(TimestampToDate(time_stamp_field)) - 1 else YearFromDate(TimestampToDate(time_stamp_field))

Similarly

Code: Select all

W: if MonthFromDate(TimestampToDate(time_stamp_field)) >=2 and MonthFromDate(TimestampToDate(time_stamp_field)) <= 12 then YearweekFromDate(TimestampToDate(time_stamp_field)) - YearweekFromDate(YearFromDate(TimestampToDate(time_stamp_field)) : "-01-31") else YearweekFromDate(TimestampToDate(time_stamp_field)) + YearweekFromDate(YearFromDate(TimestampToDate(time_stamp_field)) : "-12-31") - YearweekFromDate(YearFromDate(TimestampToDate(time_stamp_field)) : "-01-31")

[kcbland]

TIME dimension. Read Kimball. Solves all problems.

[kcbland]

TIME dimension. Read Kimball. Solves all problems.

[Madhavan VM]

Sud,

The solution proposed by you does not work for the dates 2008-1-1 and 2008-2-1, to name a few. I am looking into it if i can fine tune your solution.

Ajith

[chulett]

Time. Dimension. Seriously.

[Madhavan VM]

I googled for Time dimension, Kimball and didn't get an useful article with respect to my question. Is there a specific key word that i should look in. Thanks!

[kcbland]

Really?? I googled "ralph kimball time dimension" and the first listed item is his most famous white paper on it? You need to read it to understand your solution is not ETL but architecture. A data warehouse uses a Time dimension to facilitate the calculations you are charged with doing. We've talked about your particular query and similar others for years and my steady answer is always to implement a Time dimension and use it in your calculations. I'm not saying you have to do foreign surrogate key substitution, but for heavens sake at least look up your dates in the table to get the attributes you require.