HEAD function inside aggregation function SUM

Question

How do I change this script so that HEAD is not inside the aggregation function SUM?

The reason I'd like to do this is that I'm being warned that:

Function 'Head' was used inside aggregation function - this disables block computation mode

WITH 
  SET [LatestDate] AS 
    [Date].[Calendar].[Date].&[20060611] 
  MEMBER [Measures].[Sales] AS 
    [Measures].[Internet Sales Amount] 
  MEMBER [Measures].[Sales_EquivMTD] AS 
    Sum
    (
      Head
      (
        [Date].[Calendar].CurrentMember.Children
       ,11
      )
     ,[Measures].[Sales]
    ) 
SELECT 
  {
    [Measures].[Sales]
   ,[Measures].[Sales_EquivMTD]
  } ON COLUMNS
 ,NON EMPTY 
    [Date].[Calendar].[Month].MEMBERS ON ROWS
FROM [Adventure Works];

score 1 · Accepted Answer · answered Oct 23 '14 at 21:23

1

You could replace

Head
      (
        [Date].[Calendar].CurrentMember.Children
       ,11
      )

with a range expression:

[Date].[Calendar].CurrentMember.Children.Item(0) : [Date].[Calendar].CurrentMember.Children.Item(10)

But I am not sure if that helps performance wise. And it could cause an error on members having less than 11 children.

answered Oct 23 '14 at 21:23

FrankPl

13,205
2
14
40

@frankPI +1 thanks - wonder why Mosha's tool giving me this message? There must be a valid alternative approach. Maybe since he last updated the tool the engine has been changed so this runs in block mode. – whytheq Oct 24 '14 at 12:23
If you get 2mins spare could you please check out this question: http://stackoverflow.com/questions/26588874/mdx-how-to-calculate-measure-against-dimension-without-displaying-members-in-res/26591528#26591528 . I'm sure you'll know the answer to it but I've attempted and answer and am fascinated to know why it doesn't work ... so if you could comment on my answer I'd be happy! – whytheq Oct 27 '14 at 19:27

HEAD function inside aggregation function SUM

1 Answers1