Help with an MDX Calculations please

Last Post 25 May 2004 11:44 PM by wntsimon. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
wntsimon
New Member
New Member

--
17 May 2004 01:17 AM
We need to compare the current weeks Sales figures with the same week of the previous year. Simply enough, but as well as going back one year we also need to go back a previous season code....

C1 - Conitinuous ( never alters)
E3 - Spring 2003
E4 - Spring 2004

So when you compare Week 200420 with 200320 you also need to switch seasons... For example [Week:200420, Season:E4] compares with [Week:200320, Season:E3].
With C1 season, however this is not required. Only the week on week comparison is necessary.

I have a Time.CalendarWeeks Dimension, and a Seasons Dimension that go like....

C1 - Continuous
E2 - Spring 2002
E3 - Spring 2003
E4 - Spring 2004
H2 - Winter 2002
H3 - Winter 2003
H4 - Winter 2004


I wrote Calculated Expression which does the job nicely, but it falls over if more than one Season is selected in the Page filter in Excel.

My solution is...

iif( LEFT([Item Seasons].CurrentMember.Name,1) = "H" OR LEFT([Item Seasons].CurrentMember.Name,1) = "E" ,
([Measures].[Sales Total Qty],[Item Seasons].PrevMember,ParallelPeriod([Time].[CalendarWeek].[Year])),
([Measures].[Sales Total Qty],ParallelPeriod([Time].[CalendarWeek].[Year])))

How can I modify this to cope with multiple season selections, as it seems to take the first in the list as the 'CurrentMember' and ignores further selections.
      yongliyang
      New Member
      New Member

      --
      20 May 2004 07:41 AM
      What do you mean by falls over if more seasons are selected? (more details needed)

      It seems that you calendarweek and season dimension selection have to be synchornized, otherwise, you may get the wrong value returned.
      wntsimon
      New Member
      New Member

      --
      25 May 2004 11:44 PM
      Hi again,

      To be more precise with with problem...

      My calculation above is used for a Calculated Member in Analysis Manager.
      We use Excel (XP/2002) Pivot-tables to view the Cube Data.

      When the User Selects One season as a Page Filter, the Calculated Member works fine.
      However, when the user select multiple seasons on the page filter (A new feature in Office XP+), the Calculated Member, stops working properly and only shows Last Year figures for 'C1' and none of the additional season that may be selected.

      After playing with MDX for a few weeks, it seems Excel is doing some clever trick to allow Slicing of multiple members of the same Dimension, which is not possible with basic MDX WHERE clauses. This is probably why my [Season].CurrentMember is only referencing the First item in the Season Selection which is alway C1 (Alphabetical)
      yongliyang
      New Member
      New Member

      --
      26 May 2004 05:38 AM
      As I understand, you could only display one page selection at time. If it is like what you said that report always pick C1 (first in the selection list) when you have multiple selection and what is currently displayed in page filter is not C1 (I assume so), it maybe a defect in excel/OLAP compoenet. The workaround is to just select one season at a time.
      You are not authorized to post a reply.

      Acceptable Use Policy