|
Home > Archive > MS SQL Server OLAP > March 2006 > MDX, scope and ParallelPeriod
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 |
MDX, scope and ParallelPeriod
|
|
|
| For business reasons, I'm trying to get last year's sales using this year's
exchange rate.
I need to do this for each continent, and since each country has a separate
exchange rate, I need to sum over the countries.
However, when the sum enumerates through the countries to get last year's
sales in local currencies, the current member in the time dimension is last
year. This causes my Exchange Rate measure to return last year's exchange
rate instead of this year's.
I need to fix the scope of the exchange rate calculation to this year. At
the same time I need to fetch last year's sales.
Anyone knows how to do that?
Here is the MDX:
with
member [Measures].[ExchangeRate] as
[Measures].[Sales Local Currency] / [Measures].[Dollar Sales]
member [Measures].[Last Year Dollar Sales] as
sum(ParallelPeriod(Y
ear,1,[Calendar].currentmember,[Stores].currentmember.children),
[Measures].[Sales Local Currency] /
([Stores].currentmember,[Measures].[ExchangeRate]) // unfortunately gets
exchange rate from last year
)
select [Stores].Continents on rows,
{[Measures].[Last Year Dollar Sales]} on columns
from MyCube
where ([Stores].[Europe],[Calendar].[Day].[2006-03-22])
| |
| Deepak Puri 2006-03-27, 8:27 pm |
| How about trying something like:
[color=darkred]
member [Measures].[Last Year Dollar Sales] as
Sum([Stores].currentmember.children,
(ParallelPeriod(Year
,1,Calendar].CurrentMember),
[Measures].[Sales Local Currency])
/ [Measures].& #91;ExchangeRate])[c
olor=darkred]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.droptable.com ***
| |
|
| Hi Deepak, thanks for looking into this.
The problem with the suggestion, is that each country has a different
currency, ie each country has a separate exchange rate.
So the exchange rate should stay in the scope of the Stores dimension, but
outside of the scope of the Time dimension.
"Deepak Puri" wrote:
> How about trying something like:
>
> member [Measures].[Last Year Dollar Sales] as
>
> Sum([Stores].currentmember.children,
> (ParallelPeriod(Year
,1,Calendar].CurrentMember),
> [Measures].[Sales Local Currency])
> / [Measures].[ExchangeRate])
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex
| |
|
| Sorry Deepak,
I replied too fast to your suggestion. Please ignore my previous reply.
This is exactly what I was looking for and it solves my problem.
Many thanks !
"Deepak Puri" wrote:
> How about trying something like:
>
> member [Measures].[Last Year Dollar Sales] as
>
> Sum([Stores].currentmember.children,
> (ParallelPeriod(Year
,1,Calendar].CurrentMember),
> [Measures].[Sales Local Currency])
> / [Measures].[ExchangeRate])
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex
|
|
|
|
|