|
Home > Archive > MS SQL Data Warehousing > December 2005 > Selecting the same week day a year ago
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Selecting the same week day a year ago
|
|
| badlydressedboy 2005-12-05, 7:23 am |
| 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 Veerman 2005-12-07, 9:23 am |
| 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 & #91;mailto:badlydres
sedboy@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.
| |
| badlydressedboy 2005-12-07, 9:23 am |
| 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.
>
> ----
> Erik Veerman
> erik (at) solidqualitylearning
.com
>
> -----Original Message-----
> From: badlydressedboy & #91;mailto:badlydres
sedboy@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 Veerman 2005-12-07, 9:23 am |
| 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 & #91;mailto:badlydres
sedboy@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.
>
> ----
> Erik Veerman
> erik (at) solidqualitylearning
.com
>
> -----Original Message-----
> From: badlydressedboy
> & #91;mailto:badlydres
sedboy@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.
>
>
| |
| Peter Nolan 2005-12-16, 7:23 am |
| 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
|
|
|
|
|