 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / December 2005Selecting the same week day a year ago
Hi all, In a calculated member I need to be ale to select the same day of the week a year ago as opposed to the same date. For example, if the current day is wednesday of week 50, 2005, I need to get the wednesday of week 50, 2004. This means that a -1 parallelPeriod is useless. I have put the day of the week and the week number into member properties but this has proved to be no good as I can't say '[date].[week] = [date].CurrentMember.Properties("last_years_week")' - even though I really need to select the day using member properties as variables. Does anybody have any ideas???? T.I.A. For the day level, how about just a lag(364)? This would give you the same day of week (Wednesday of week 50) from the previous year and also work in a leap year. ---- Erik Veerman erik (at) solidqualitylearning.com -----Original Message----- From: badlydressedboy [mailto:badlydressedboy@discussions.microsoft.com] Posted At: Monday, December 05, 2005 6:37 AM Posted To: microsoft.public.sqlserver.datawarehouse Conversation: Selecting the same week day a year ago Subject: Selecting the same week day a year ago Hi all, In a calculated member I need to be ale to select the same day of the week a year ago as opposed to the same date. For example, if the current day is wednesday of week 50, 2005, I need to get the wednesday of week 50, 2004. This means that a -1 parallelPeriod is useless. I have put the day of the week and the week number into member properties but this has proved to be no good as I can't say '[date].[week] = [date].CurrentMember.Properties("last_years_week")' - even though I really need to select the day using member properties as variables. Does anybody have any ideas???? T.I.A. Erik, Thanks for that but I can't see how it handles leap years (my main problem) - sometimes it will be 364 and sometimes it will be 365 days previous. I dont want to start getting into leap year handling logic so am currently calculating the lag in SQL and using it as a member property. Al > For the day level, how about just a lag(364)? This would give you the > same day of week (Wednesday of week 50) from the previous year and also [quoted text clipped - 28 lines] > > T.I.A. Well, I may be wrong... so let me know if there's a flaw in my thinking. In a leap year, the day of the week is not affected, it just adds an extra day (to make the year 366 days). And I am assuming that you are interested in the Wednesday of week 50, even in a leap year. As an example, 2004 was a leap year. So Lets take Friday Jan 7th 2005. If we go back 364 days, we get Friday January 9th, 2004. Its still Friday, but there are 2 days in between, not one. If we had gone back 365 days over a leap year, it would be Thursday January 8th. But its Thursday, not Friday. Go to Friday January 6th, 2006... Subtract 364 days, and you get Friday Jan 7th 2005. I am assuming in this example you want the same Friday from the prior year, even if there's a leap year in-between. If this is the case, 364 would work for a leap year and non-leap year calculation. This becomes more difficult if you need to do this up the Date hierarchy (week, month, quarter, etc), but I have some ideas on that as well... ---- Erik Veerman erik (at) solidqualitylearning.com -----Original Message----- From: badlydressedboy [mailto:badlydressedboy@discussions.microsoft.com] Posted At: Wednesday, December 07, 2005 9:05 AM Posted To: microsoft.public.sqlserver.datawarehouse Conversation: Selecting the same week day a year ago Subject: Re: Selecting the same week day a year ago Erik, Thanks for that but I can't see how it handles leap years (my main problem) - sometimes it will be 364 and sometimes it will be 365 days previous. I dont want to start getting into leap year handling logic so am currently calculating the lag in SQL and using it as a member property. Al "Erik Veerman" wrote: > For the day level, how about just a lag(364)? This would give you the > same day of week (Wednesday of week 50) from the previous year and > also work in a leap year. [quoted text clipped - 17 lines] > week a year ago as opposed to the same date. For example, if the > current day is wednesday of week 50, 2005, I need to get the wednesday > of week 50, 2004. > This means that a -1 parallelPeriod is useless. > I have put the day of the week and the week number into member > properties but this has proved to be no good as I can't say > '[date].[week] = [date].CurrentMember.Properties("last_years_week")' - > even though I really need to select the day using member properties as > variables. > > Does anybody have any ideas???? > > T.I.A. Hi badlydressedboy, the way I recommend people do these things is to create a time dimension (in the underlying database) that carries all these things as text or date fields etc and then if they are needed in a cub or report just put them in there....to create them we just use a spreadsheet and load the spreadsheet.... Peter |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.