Hello everyone,
My data is weekly sales data. The date stamp on the weekly data is always the beginning of the week of sales for that week. The analyses that I do are on calendar week, quarter, month and year. So a calendar week belongs to a particular fiscal month and quarter and year. Most of the time this works really well using the functions weekname(), quartername(), monthname(), year() and weekyear(). There is an exception on occasion at the end of a year. One of those dates is the 29.12.2008.
Using the weekname() function returns that it is calendar week 01/2009 - this is correct.
Using year() it returns 2008, which is for the function description also correct, so I use the function weekyear() which returns 2009. this is correct. It means when I aggregate across the fiscal year 2009, the week starting on the 29.12.2008 is included in the aggregate. This is correct.
The functions quartername() and monthname() return Oct-Dec 2008 and Dec 2008 respectively. This is for fiscal calculation not correct. It is correct according to the function definition as it looks at the month and year and not the week. It should (for fiscal analyses) return Q1 2009 and Jan 2009 respectively.
So, without having to write a workaround to catch these year end problems, does anyone know how those functions could be tweeked to deliver the correct (fiscal) information? And I would like to have these in the script rather than in the charts. Or are there other functions that I have missed that I should be using?
Oh, I am using 9sr5.
Best Regards,
Petra
