How do I create a dimension that returns the facts for the
last 3 months.

If I select August I want the facts for June + July +
August.

Sept would return the facts for July + August + Sept

Do you want an aggregation of the facts for the last 3 months?

tom @ the domain below
www.tomchester.net


"Jeff Grundy" <jeffgrundy (AT) nospam (DOT) com> wrote

Hi Jeff,

Thanks for your post. After reading your description, I am not sure what
your accurate concern is. Can you describe it in detail? Do you mean that
you want to add and retrieve the values of the facts for the last 3 months?
If so, calculated member and these functions will help you a lot.

SUM returns the sum of a numeric expression evaluated over a set.
LastPeriods returns a set of members prior to and including a specified
member.

For additional information regarding these functions, please refer to the
following article on SQL Server Books Online.
Topic: "Calculated Members"
Topic: "LastPeriods"
Topic: "Sum"

In addition, it would be great if you could make an example on the sample
database, FoodMart 2000. It will help us a lot to understand what action
you want to perform and what result you want to get.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Yes I am trying to agregate the facts for the last 3 months

In foodmart if I were to try to create it I would do something like this

In the properties of the member

Member Name Column = Format("time_by_day"."the_date",'mmmm'
Member Key Column = between DatePart('m',"time_by_day"."the_date") and DateAdd('m',-2, "time_by_day"."the_date"

Or in psudeo-syntax
Member Key Column = (This Month) and (Last Month) and (2 months ago

This dosen't work but it demonstrates what I am trying to do

----- Tom Chester wrote: ----

Do you want an aggregation of the facts for the last 3 months

tom @ the domain belo
www.tomchester.ne


"Jeff Grundy" <jeffgrundy (AT) nospam (DOT) com> wrote in messag
news:051b01c3a882$a136bdc0$a101280a (AT) phx (DOT) gbl..

In foodmart if I were to try to create it using 'LastPeriods' I would do something like this

In the properties of the membe

Member Key Column = LastPeriods(2,"time_by_day"."the_date"
Member Name Column = Format("time_by_day"."the_date",'mmmm'

That dosen't work but it demonstrates what I am trying to do

It is similar to having a Quarter dimension with months 'below' it.

When you view quarters
Q1 it contains months Jan,Feb,Mar
Q2 contains months Apr,May,Ju

In the dimension I need:
T3Jan03 contains months Jan03, Dec02, Nov0
T3Feb03 contains months Feb03, Jan03, Dec02


----- Michael Shao [MSFT] wrote: ----

Hi Jeff

Thanks for your post. After reading your description, I am not sure what
your accurate concern is. Can you describe it in detail? Do you mean that
you want to add and retrieve the values of the facts for the last 3 months
If so, calculated member and these functions will help you a lot

SUM returns the sum of a numeric expression evaluated over a set
LastPeriods returns a set of members prior to and including a specified
member

For additional information regarding these functions, please refer to the
following article on SQL Server Books Online
Topic: "Calculated Members
Topic: "LastPeriods
Topic: "Sum

In addition, it would be great if you could make an example on the sample
database, FoodMart 2000. It will help us a lot to understand what action
you want to perform and what result you want to get

Thanks for using MSDN newsgroup

Regards

Michael Sha
Microsoft Online Partner Suppor
Get Secure! - www.microsoft.com/securit
This posting is provided "as is" with no warranties and confers no rights

You don't need a new dimension, all that's needed is one (or more)
calculated members. Here's a foodmart example. The LastPeriods function is
using currentmember of Time to determine ending period.

WITH MEMBER Measures.[Last3] AS
' Sum(LastPeriods(3, Time.CurrentMember), Measures.[Store Sales]) '

tom @ the domain below
www.tomchester.net


"Jeff Grundy" <anonymous (AT) discussions (DOT) microsoft.com> wrote