Hi,

I need to compare current year vs up to relative date of previous year.
p.e. if today=5/5/2006 I want to compare let's say 'sales' of current year
(up to 5/5/2006) vs 'Sales' of previous year up to 5/5/2005.

Any ideas?

Thank you in advance
D.E.

Thank you Deepak.
My problemis that I use expressions in calculated members builder.
So I cannot use this select.

I know how parallelperiod works but I cannot apply in expressio in order to
have this difference betwwen [2005}.Sales (up to 16/5/2005) and [2006].Sales
(up to today='16/5/2006)

(I work with SQL 2000 Analysis Server)

Regards,
D.E.


"Deepak Puri" wrote:

In AS 2000 Calculated Member Builder, you could create:

-> [Measures].[YTDSales]:
Sum(YTD([Date].CurrentMember), [Measures].[Sales])

-> [Measures].[PYTDSales]:
iif(ParallelPeriod([Date].[Year]) is Null,
Null, ([Measures].[YTDSales],
ParallelPeriod([Date].[Year])))

-> [Measures].[YTDSalesDiff]:
([Measures].[YTDSales] - [Measures].[PYTDSales])


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

Thank you again for your time.
I think this :

iif(ParallelPeriod([Date].[Year]) is Null, Null, ([Measures].[YTDAmount],
ParallelPeriod([Date].[Year])))

will not bring what I need, but will bring the total for the previous year.
What I want is to bring the sum () for the previous year up to the Today
date minus 1 year.

p.e. sum (Sales of previous year up to 17/5/2006) not sum (of the whole
previuous year)

Regards
D.E.

"Deepak Puri" wrote:

Since you're using AS 2000, try this Foodmart query:

Member [Measures].[YTDSales] as
'Sum(YTD([Time].CurrentMember), [Measures].[Warehouse Sales])'
Member [Measures].[PYTDSales] as
'iif(ParallelPeriod([Time].[Year]) is Null,
Null, ([Measures].[YTDSales],
ParallelPeriod([Time].[Year])))'
Member [Measures].[YTDSalesDiff] as
'([Measures].[YTDSales] - [Measures].[PYTDSales])'

select {[Measures].[Warehouse Sales], [Measures].[YTDSales],
[Measures].[PYTDSales], [Measures].[YTDSalesDiff]} on 0,
[Time].[Quarter].Members on 1
from Warehouse

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***