More on tuning YTD-style calculations

I’m indebted to Deepak Puri for coming up with a much less complex MDX expression for finding the common ancestors in a set of months. Here it is:

WITH
/*The initial time range expressed in months*/
SET MYRANGE AS ‘{[Time].[1997].[Q1].[3] :[Time].[1998].[Q3].[7]}’
/*Test whether Time member is a subset of range */
Member [Measures].[InRange] asExcept(Descendants([Time].CurrentMember,, LEAVES), MYRANGE).Count = 0′
/* Include all members in range whose parents are not in range */
SET MYNEWRANGE ASFilter([Time].Members, [Measures].[InRange] AND Not ( [Measures].[InRange], [Time].Parent))’
/*Helper calculated members to display the steps*/
MEMBER MEASURES.STEP1 ASSETTOSTR(MYRANGE)’
MEMBER MEASURES.STEP5 ASSETTOSTR(MYNEWRANGE)’
/*Show working*/
SELECT {MEASURES.STEP1, MEASURES.STEP5} ON 0 FROM SALES

8 responses

  1. Here is small optimization. Instead of Filter(Time.MEMBERS,…) you can do Filter(Generate(MYRANGE,Ascendants(Time.CurrentMember)),…) which would spare iterating over completely unrelated periods. However, I think there must be simpler solution to this problem…

  2. Hi Chris et al,I am loving this, BUT, I am having some trouble implementing it. You see, my Time dimension (called \’Year\’) has the following levels:(All), Year, Half Year, Quarter, Month, Week, DayI am trying to get the sales for the last 52 weeks from Year.CurrentMember (at the Week Level).Here is my code thus far:WITH/*The initial time range expressed in weeks*/ SET MYRANGE AS \’{lastperiods(52,[Year].[2005].[First Half].[Qtr 2].[May].[Week 15])}\’/*Test whether Time member is a subset of range */ Member [Measures].[InRange] as \’Except(Descendants([Year].CurrentMember,, LEAVES), MYRANGE).Count = 0\’ /* Include all members in range whose parents are not in range */ SET MYNEWRANGE AS \’Filter([Year].Members, [Measures].[InRange] AND Not ( [Measures].[InRange], [Year].Parent))\’ /*Helper calculated members to display the steps*/ MEMBER MEASURES.STEP1 AS \’SETTOSTR(MYRANGE)\’ MEMBER MEASURES.STEP5 AS \’SETTOSTR(MYNEWRANGE)\’ /*Show working*/ SELECT {MEASURES.STEP1,MEASURES.InRange, MEASURES.STEP5} ON 0 FROM SALESCubeAs you can see, I have not changed much, just the MYRANGE bit, replaced \’Time\’ with \’Year\’, and am selecting InRange.MYRANGE selects the weeks I am interested in – so that is all good.InRange shows 0 (zero) – (I am not MDX-smart enough to work out exactly what you\’re doing here) – InRange is 1 in your example.MYNEWRANGE shows {} – which is a shame.What do I need to do to get this logic working with my Year dimension?? Please help!!Thanks,Matt.

  3. Matt,I’ve taken a look at the MDX you put in the comment and I think I can see what’s going on – the test to see whether a member is in the initial range finds the descendants at leaf level, but your initial range isn’t at the leaf (day) level, it’s at the week level. I think the following should work for you:WITH /*The initial time range expressed in weeks*/ SET MYRANGE AS \’{lastperiods(52,[Year].[2005].[First Half].[Qtr 2].[May].[Week 15])}\’ /*Test whether Time member is a subset of range */ Member [Measures].[InRange] as \’Except(Descendants([Year].CurrentMember,[Year].[Week]), MYRANGE).Count = 0\’ /* Include all members in range whose parents are not in range */ SET MYNEWRANGE AS \’Filter([Year].Members, [Measures].[InRange] AND Not ( [Measures].[InRange], [Year].Parent))\’ /*Helper calculated members to display the steps*/ MEMBER MEASURES.STEP1 AS \’SETTOSTR(MYRANGE)\’ MEMBER MEASURES.STEP5 AS \’SETTOSTR(MYNEWRANGE)\’ /*Show working*/ SELECT {MEASURES.STEP1,MEASURES.InRange, MEASURES.STEP5} ON 0 FROM SALESCube

  4. Hi Chris, thanks a lot for your reply. I have managed to get this working, BUT…I am actually trying to add a calculated measure to the cube, so that I can use it in Excel with Pivot Table Services. The steps I went through were to:1. Create a Named Set, called \’Last52Weeks\’, which literally has the last 52 members along the Week level of the Year dimension.2. Create the \’InRange\’ calculated measure, as per your example.3. Create another Named Set, called Consolidated52Weeks (the equiv of MYNEWRANGE)4. Create a Calc Measure called SalesLast52Weeks, which is just: sum(Consolidated52Weeks, [Measures].[Sales$])Now, if in my Last52Weeks named set, if I get the last 52 weeks from "a hardcoded member", then I get the results I expect. But., if i use \’Year.CurrentMember\’, which I really really want to as it needs to be dynamic, then I get nothing at all.I am guessing there is some fundamental difference between the code example you have supplied which is intended to be run as an \’external\’ statement, and converting this into something internal to the cube – but I don\’t know what that is!! Any ideas?Thanks,Matt.

  5. I\’m afraid that named sets, as you\’ve discovered, aren\’t dynamic – they will always return the same result as the first time they\’re evaluated. I only used them in the examples to make the code easier to read. If you want to make your calculated member work you\’ll need to include everything, including what you currently have in your set, in the calculated member definition. If I have a spare moment this week I\’ll try to put together an example for you…

  6. Hi, thanks for that Chris. I tried putting everything in one single calculated member, (Including the \’InRange\’ part) and didn\’t have much luck. I\’d be overjoyed if you are able to put a working example together.Thanks a LOT,Matt.

  7. Here\’s the Foodmart 2000 example as a calculated measure:WITHMEMBER MEASURES.TEST AS \’SETTOSTR(FILTER(GENERATE(YTD() AS MYMONTHS, ASCENDANTS(TIME.CURRENTMEMBER)), COUNT(EXCEPT(DESCENDANTS(TIME.CURRENTMEMBER,,LEAVES), MYMONTHS))=0ANDIIF(TIME.CURRENTMEMBER.LEVEL.ORDINAL>0,COUNT(EXCEPT(DESCENDANTS(TIME.CURRENTMEMBER.PARENT,,LEAVES), MYMONTHS))<>0, TRUE)))\’SELECT {MEASURES.TEST} ON 0,TIME.MONTH.MEMBERS ON 1FROM SALES

  8. I suppose this one is slightly better, because of absenece of DESCENDANTS(,,LEAVES)
     

    with
    — initial set.
    set set1 as {[Date].[Calendar].[Month].&[2001]&[7]:[Date].[Calendar].[Month].&[2003]&[8]}
    set set2 as generate(set1, {ascendants([Date].[Calendar].currentmember)})
    set set3 as generate({set1.Item(0).Item(0).PrevMember, tail(set1,
    1).Item(0).Item(0).NextMember},
    {ascendants([Date].[Calendar].currentmember)})
    set set4 as set2 – set3
    set set5 as filter(set4, [Date].[Calendar].Level.Ordinal = 0 or ({[Date].[Calendar].currentmember.parent} –
    set4).count > 0)
    select {} on 0,
    set5 on 1
    from [Adventure Works]
     
     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: