# 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]..[Q1]. :[Time]..[Q3].}’
/*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. Mosha says:

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. Matthew says:

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]..[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. Chris says:

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]..[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. Matthew says:

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. Chris says:

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. Matthew says:

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. Chris says:

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. chtepa says:

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

with
— initial set.
set set1 as {[Date].[Calendar].[Month].&&:[Date].[Calendar].[Month].&&}
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