October 16, 2003 at 7:05 am
I am unfortunately still seriously struggling with defining a calculated member in Analysis Services that can correctly identify a set of data based on the intersection of two time dimensions.
Example Scenario:
One time dimension reflects when a project starts (START_TIME dimension), another time dimension reflects when a project finishes (END_TIME dimension). Measures can count the number of projects, and the dollars associated with the projects. (The fact table contains two time fields: one for start_time, one for end_time).
I'd like to identify a set of projects that begin within a certain range of months and end within a range of months: for example, projects that started anytime between Jan and March, and ended anytime between Jan and June. I have no problem writing a sql query to obtain this info, but an MDX expression seems elusive.
Ideally, I don't want to hardcode the dates, but rather be dynamic (e.g, currentmember).(FYI - Month is the lowest level in the time dimensions). It seems like I should be using the YTD function such that if the Start_Time dimension has March as the currentmember, then the YTD function will pickup all projects that started between Jan thru March. (This I can define: sum(Ytd([START_TIME],cases]))
Likewise, the set of data that intersects from the END_Time dimension where the current member is June, would be defined as YTD producing a set that spans JAN - JUNE from the END_TIME Dimension.
So, what is the mix of functions/syntax necessary to create a single calculated member that can reflect the intersection of YTD START_TIME with YTD END_TIME, such that the number of projects can be summed?
Going back to my original example: What are the number of projects that started anytime between Jan-March, and finished anytime between JAN-JUNE?
Again, many, many thanks in advance for help.
--Peter
October 20, 2003 at 12:00 pm
This was removed by the editor as SPAM
October 20, 2003 at 11:07 pm
Peter,
Not sure if this will do exctly what you are after (I ran out of time trying to work up a sample cube similar to yours), but I'm hoping it is close.
sum(CROSSJOIN(Ytd([START_TIME]), Ytd([END_TIME])),[Measures].[cases])
Really, this should give you the same thing as crossing the two dimensions against each other on rows and columns.
HTH,
Steve.
Steve.
October 21, 2003 at 12:41 pm
Steve:
Your suggestion almost works for me:
sum(CROSSJOIN(Ytd([START_TIME]), Ytd([END_TIME])),[Measures].[cases])
Perhaps, though you can suggest the correct "tweek" it needs...
The limitation of the above expression is that the YTD function always stays limited to within a specific year and can't look further back in time.
For example: Any projects that Start in March2002 but don't end until Feb2003 won't be correctly summed; instead the result will only sum those projects that ended in either Jan2003/Feb2003 rather than summing all projects that started March2003 and ended anytime up thru Feb2003.
So, I've been attempting to not use YTD(END_TIME) but instead something like the following: SUM(Crossjoin(Ytd([START_TIME]),{[END_TIME].CURRENTMEMBER.PARENT.FIRSTCHILD:[END_TIME]},Measures].[CASES])
Basically, I'm trying to establish a Set for END_TIME that grabs the earliest END_TIME that exists in the data and create a range up through the current chosen END_TIME. (Technically, the earliest END_TIME is 01/01/1999). Unfortunately, the data seems to sum correctly at a month level in an Excel Pivot table, but the subtotals and grandtotals in the pivot are junk. (Obviously, .currentmember.parent.firstchild isn't quite right.)
Again, the present time dimensions are year, qtr, month (where month is expressed as yyyymm).
So, if you are able to suggest how to create a Set for END_TIME that always begins with the earliest END_TIME and covers dates up through a user_selected END_TIME, I would be greatly indebted to you!
October 21, 2003 at 8:33 pm
Hi Peter,
This *almost* works for me, being a perfectionist I don't like that it doesn't work at any level other than the lowest i.e. Month, but, it *does* work at that level. Maybe a little more investigation would make it work at all levels which would be nicer.
Basically you want to use TopCount(Descendants(«Member», «Level»), 1) or in a cleaner form OpeningPeriod([«Level»[, «Member»]])
So, what I got to work for me was ->
sum(CROSSJOIN(Ytd([START_TIME]), {OpeningPeriod([END_TIME].[Month],[Shipped Date]):[END_TIME].CurrentMember}),[Measures].[cases])
HTH,
Steve.
Steve.
October 23, 2003 at 8:19 am
Steve:
Your recent suggestion is exactly where I ended up the other day - amazing. (Eventhough I'm new to mdx, I'm glad I can keep up with a pro like yourself.) I agree that having the expression only work at the month level is not completely satisfactory.
Well, good news -- I've solved the problem where the equation works on each level, but I think the syntax is cumbersome. Perhaps there's a cleaner way to do the following expression:
iif([END_TIME].currentmember.level.ordinal=3,
SUM(Crossjoin(Ytd([START_TIME]),{[END_TIME].CURRENTMEMBER.parent.parent.firstsibling.firstchild.firstchild:[END_TIME]}),[Measures].[CASES]]),
iif([END_TIME].currentmember.level.ordinal=2,
SUM(Crossjoin(Ytd([START_TIME]),{[END_TIME].currentmember.parent.firstsibling.firstchild:[END_TIME]}),[Measures].[CASES]]),
SUM(Crossjoin(Ytd([START_TIME]),{[END_TIME].currentmember.firstsibling:[END_TIME]}),[Measures].[CASES]])))
Again, many thanks for helping out!
--Pete
October 23, 2003 at 1:31 pm
I've actually got the code trimmed down: i.e., If..Thens has been deleted.
Here's a cleaner expression:
SUM(Crossjoin(Ytd([START_TIME),{[END_TIME].CURRENTMEMBER.LEVEL.MEMBERS.ITEM(0):[END_TIME]}),[Measures].[CASES])
The above mdx efficiently/accurrately crossjoins YTD(start_time) with a set that covers the first member at any level in all of End_Time to the current End_Time.
Hope this info is helpful to anyone else. FYI - Spofford's book MDX Solutions has similar info on page 58/59.
--Pete
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply