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

"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 [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.
>
>

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.
>
> ----
> 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.
>
>

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