December 13, 2010 at 10:29 am
I am fairly familiar with SSAS, but by no means an expert so any help you can provide would be greatly appreciated. I am trying to report comparable sales between restaurants and I cannot figure out how to handle the following two items.
Issue 1:
I am working on a cube that uses the 4-4-5 accounting method. I have my time (or Date) dimension setup and working properly. The problem I have is that every 5 years there are 53 weeks in the fiscal year. I need help figuring out how to handle comparable sales when one year has 52 weeks and another has 53 weeks.
Year 2010 has 52 weeks
Year 2009 has 53 weeks
When comparing weekly (daily, quarterly, etc.) sales of 2010 with 2009 the calendar days offset by one week.
For example:
Week 1 of 2009 is Dec 29 2008 - Jan 4 2009
Week 1 of 2010 is Jan 4 2010 - Jan 10 2010
However, when I compare week 1 2010 with week 1 2009 I need to compare it with week 2 of 2009 (Jan 5 2009 - Jan 11 2009) because of the 53 weeks in fiscal year 2009. Currently my calculated member compares week 1 2010 with week 1 2009. Normally this would be great, but in the case of a 53 week year this doesn’t work.
If possible, I would like this to built into the cube by modifying my time dimension so complicated MDX doesn't have to be written for cube browsing.
Issue 2:
I need to determine when a restaurant is in comp. What that means is that they only include a restaurant in comparable sales when the restaurant has been open for 24 months.
Example: Restaurant opens 8/15/2007. With a 24 month comp, the first time that the location is factored into comp for each time interval is the first full interval when they are in comp. The following table shows when the example restaurant would be in comp for each time interval.
Time Interval Date Notes
Day 8/24/09 Two years from the open date.
Week 8/24/09 Next Full fiscal week (If it opened on the first day of the week comp24 will be that week two years later.)
Period 8/24/09 Next full fiscal period. (If it opened on the first day of the period comp24 will be that period two years later.)
Quarter 9/28/09 Next full fiscal quarter. (If it opened on the first day of the Quarter comp24 will be that Quarter two years later.)
Year 1/4/10 Next full fiscal year. (If it opened on the first day of the Year comp24 will be that Year two years later.)
How would I figure out what a restaurant’s 24 month comparable date within a cube and include (or exclude) sales data if the user wants to look at a week, period, quarter or year comparable sales information?
December 14, 2010 at 11:43 am
I would say tell the restaurant to use a more "Famous" calendar
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply