Hi,

I'm having a problem concerning diplaying Previous Year Values when a Filter
is set on time dimension.

Lets assume a company has quarterly sales of exactly 1000.- in 2003 and 2004.
The Pivot-Table filtered on the first 3 quarters looks as follows:

SALES S_YTD S_PREV S_PREV_YTD
2004
Q1 1000 1000 1000 1000
Q2 1000 2000 1000 2000
Q3 1000 3000 1000 3000
SUM 2004 3000 3000 4000 4000
==== ====

As you can see, the values for Sales Previous Year (YTD) are calculated on
the total of 2003 as for sure is correct concerning my formula for Prev. Year
Values:

([MEASURES].[Sales], ParalellPeriode([TIME].[YEAR],1))

How does the MDX-formula havo to look like, when previous values measures
should also care about the Filter set in current year ... displaying
following information:

SALES S_YTD S_PREV S_PREV_YTD
2004
Q1 1000 1000 1000 1000
Q2 1000 2000 1000 2000
Q3 1000 3000 1000 3000
SUM 2004 3000 3000 3000 3000
==== ====


Thx in advance for your appreciated answer

Monte

It's not clear what "the Filter set in current year" is. Assuming that
the query is filtering out empty members (in this case, Q4 2004), and
that the lowest [Time] level at which data is loaded is [Month]), then
will this work?

[Time].CurrentMember, [Time].[Month])),
([MEASURES].[Sales], ParalellPeriod([TIME].[YEAR])))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thx for Your Answer, but it is not exactly what I meant ... I'll get more
detailled:

Time Dimension in this scenario consists of (Year, Quarter, Month, Day).
Data is loaded always on day level and aggregated up. For each Quarter in
2003 and 2004 there is Salesamount of 1000.-

(filter) has been set to [2004].[Q1], [2004].[Q2], [2004].[Q3] .

So actual Sales Sum for the total Level (2004) Sums up the first 3 included
Quarters (as shown in the example).

the Prevois Year Value should also care about the filter, displaying the
total of only the filtered members in 2003. Or to make it more visible
(assuming sales for Q1: 1000, Q2:2000 and so on, in both of the years)

SALES S_YTD S_PREV S_PREV_YTD
2004
Q1 1000 1000 1000 1000
Q2 2000 3000 2000 2000
Q3 3000 6000 3000 3000
SUM 2004 6000 6000 10000 10000
==== ====

10000 is wrong in this case, it also should display 6000, summing up Q1-Q3
in the last year




"Deepak Puri" wrote:

Assuming that your client tool is Excel Pivot Table, I logged the MDX
queries for a similar scenario in the Foodmart Warehouse cube (contains
1997 and 1998 data). Based on the MDX queries, it looks like Excel is
requesting complete year (1998) data, even when only Q1-Q3 are selected
on rows. So Excel must be doing 1998 summation separately for [Warehouse
Sales], so that only Q1-Q3 are included.

However, as you found, the entire Previous Year results are used, as
returned from MDX - I can't explain why. Here is how I did the filtered
sum for Previous Year - this only works when Time is on rows of Pivot
Table, and for single level of DrillDown (Year->Quarter). It could be
enhanced to work on columns and for multiple levels:

- [Measures].[SalesYTD] :
Sum(YTD(), [Measures].[Warehouse Sales])

- [Measures].[SalesPrev] :
iif(Count(Intersect([Time].Children,
Extract(StrToSet("Axis(1)"), [Time]))) = 0,
([Measures].[Warehouse Sales], ParallelPeriod([Time].[Year])),
Sum(Intersect([Time].Children,
Extract(StrToSet("Axis(1)"), [Time])),
([Measures].[Warehouse Sales], ParallelPeriod([Time].[Year]))))

- [Measures].[SalesPrevYTD] :
Sum(YTD(), [Measures].[SalesPrev])


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi,
this is an excellent approach, working in AS-Manager and Excel even on all
dimension levels of time, but unfortunately our main Frontend are the OWC,
with which this formula doesn't work ( i believe it's the StrToSet("Axis(1)")
Part)

I once again figured out, that MDX isn't quite my domain :-|

How can i make a Set out of Time-Dimension IncludedMembers (especially with
Web components) not depending if the dimension is located in filter, rows or
columns?

I just cant get this one to work...

Greetings and thx again
Monte


"Deepak Puri" wrote:

Now I have no time to test this idea, but hope the help. Remember that you
have to flush cached data for each test.

[S_PREV] =
IIF(SetToStr(Intersect(Time.CurrentMember.Children , StrToSet("AXIS(1)"))) =
"{}", (ParallelPeriod(Year, 1), [SALES]),
Sum(Intersect(Time.CurrentMember.Children, StrToSet("AXIS(1)")), [S_PREV]))

Ohjoo Kwon


"Monte" <Monte (AT) discussions (DOT) microsoft.com> wrote

I'm sorry, Deepak.

I did not read your answer before my anwser. Now I read your answer and
found out my idea is the same as yours.

Ohjoo


"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote

Well... It looks like that OWC and Excel recognize the Axis sequence
differently.

How about changing StrToSet("Axis(1)") to StrToSet("Axis(0)") ?


Ohjoo Kwon


"Monte" <Monte (AT) discussions (DOT) microsoft.com> wrote

Thx alot, now both of your versions work, I guess the problem was the older
cached version.

Now how to design the mdx if time dimension is set and filtered in filter or
column area?
Anything like ("ROWAXIS") or ("FILTERAXIS") ?

And what about if the customer wants to combine multiple dimensions in Row
(before or after the time dimension like: Year - customer )

Is this still realistic solvable or too complex?

Best regards

"Ohjoo Kwon" wrote:

I just tested out this situation with our previous year values and ..
the same situation.

I really wonder, why no user before came across it ... and I'm afraid
they will, and want an "ad hoc" solution ...

Is there no standard multidimensional approach, with which it doesn't
matter how dimensions are combined or where they are set? Am I wrong
considering this case as common and the miscalculation as rather severe
bug??

Bye Devl

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