• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2490
  • Last Modified:

TY Sales and LY Sales Columns do not Allow Drilling into Time Dimension

Hello,

Thanks to this forum I have been able to create calculated member formulas that break out TY Sales and LY Sales into two distinct columns. The formulas I used are shown below.

TY Sales = ([Time_LWPA].Currentmember, [Measures].[Dollars Sold])
LY Sales = ([Time_LWPA].Previousmember, [Measures].[Dollars Sold])

My time dimension is:
[Time_LWPA]
[L Year]
[L Month]
[L Week]
[L Day]

My problem is that whenever a specific month, week, etc. is selected, the columns still show sales for the entire year.

What is wrong with my formula?


0
btrieve
Asked:
btrieve
  • 7
  • 5
1 Solution
 
btrieveAuthor Commented:
I have reviewed the following solution for my problem, but cannot make it work with the time dimension levels I gave in my last post.

Can you help?

http://www.experts-exchange.com/Microsoft/Applications/Q_24046094.html

0
 
agandauCommented:
Are you specifying the [Time_LWPA] slice in the columns/rows or in the WHERE clause.

Also, what is your expected behavior?  It seems to me that if you drill into 2009 to say, Jan 2009, that your measure expressions should display Jan 2009 and Dec 2008, respectively.

It sounds like MDX is starting to click for you.  You're going to be an expert on this soon.
0
 
btrieveAuthor Commented:
The formula I posted is the entire formula I am using. I have read about selecting the columns/rows, but haven't been able to use the syntax successfully.

What I want is that when a user selects Jan 2009, TY Sales will show only sales from Jan 2009 and LY Sales will show Jan 2008 sales.

How can I do this? I'd like to do it for all time levels:
[Time_LWPA]
[L Year]
[L Month]
[L Week]
[L Day]
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
agandauCommented:
Is there a client tool the users are browsing the cube with?
0
 
btrieveAuthor Commented:
They are using Excel.
0
 
agandauCommented:
In the design of the dimension, are the levels arranged as a hierarchy?

Where I'm going with this is, each attribute of a dimension is actually treated as its own dimension.  So, if the attributes of the dimension are all visible individually within the client, e.g. [Time_LWPA].[L Year] is visible, as well as [Time_LWPA].[L Month], etc.  Then each of these can be treated as it's own dimension.

Furthermore if there's a hierarchy set up (something like [Time_LWPA].[My Date Hierarchy], then it too, can be treated as a separate dimension.  If that's the case I'd change the definitions of your measures to:

TY Sales = ([Time_LWPA].[My Date Hierarchy].Currentmember, [Measures].[Dollars Sold])
LY Sales = ([Time_LWPA].[My Date Hierarchy].Previousmember, [Measures].[Dollars Sold])

0
 
agandauCommented:
One other note, in the case of the TY Sales measure

TY Sales = ([Time_LWPA].[My Date Hierarchy].Currentmember, [Measures].[Dollars Sold])

Is actually the same as

TY Sales = ([Measures].[Dollars Sold])

Since the CurrentMember of each dimension is implicitly included in your tuple.  I'm not recommending you change it, in fact I think it makes the code more declarative of the purposes of your measures.  Just throwing in a hopefully educational tidbit.

0
 
btrieveAuthor Commented:
I tried this and it isn't behaving properly.

I created a time hierarchy as you suggested with the following:
[Time_LWPA]
[L Year]
[L Month]
[L Week]
[L Day]

It is showing the previous member, as the formula would suggest, so for a given day, it shows me the previous day for LY Sales.

What I need is the same day the previous year, same month previous year, etc. How is this accomplished?
0
 
agandauCommented:
Ah, you're looking for ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression ] ] ] )

Read all about it here:
http://technet.microsoft.com/en-us/library/ms145500.aspx

Note that this function returns a member of the time dimension, so you'll still need to couple it with the [Measures].[Dollars Sold] measure (which you're already doing).

LY Sales = (ParallelPeriod([Time_LWPA].[L Year], 1, [Time_LWPA].CurrentMember), [Measures].[Dollars Sold])

or just (because 1 and the time dimension's current member are the default)

LY Sales = (ParallelPeriod([Time_LWPA].[L Year]), [Measures].[Dollars Sold])

the portion ParallelPeriod([Time_LWPA].[L Year], 1, [Time_LWPA].CurrentMember) is saying, starting from my currentmember give me the comparable member going back [1] [L year].



0
 
btrieveAuthor Commented:
This looks like a great solution. Thank you.

Why when I try to use it does it tell me it "cannot find [Time_LWPA].[L Year] in a name binding function"?
0
 
btrieveAuthor Commented:
I figured it out, I needed to include the extension of my time dimension hierarchy, e.g.

(ParallelPeriod([Time_.Time_L].[Lowes Year], 1, [Time_L.Time_L].CurrentMember), [Measures].[Dollars Sold])
0
 
btrieveAuthor Commented:
Great job. Thanks for your assistance.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now