Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

WriteBack Weighted Allocation doesn't work properly Expand / Collapse
Author
Message
Posted Wednesday, March 12, 2014 2:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 19, 2014 2:35 AM
Points: 57, Visits: 88
I have an issue about weighted allocation. I'm trying to allocate on leafs of a user defined hierarchy which has the dimension key on the leaf level. The allocation have to be done based on the rolling 12 months. Below is the script I'm using on SSAS 2012:

UPDATE [CUBE]
SET ( [Time].[YM].[Month Name].&[201312] -- leaf
, [Dim1].[Hierarchy1].[Level3].&[101] -- to be allocated on the leafs ([Dim1].[Hierarchy1].[Level4])
, [Measures].[Sales TG] -- measure
) = 10000
USE_WEIGHTED_ALLOCATION BY -- get share of each leaf from level4 based on the total level3
SUM((PARALLELPERIOD([Time].[YM].[Month Name], 11, [Time].[YM].[Month Name].&[201312]):[Time].[YM].[Month Name].&[201312]
, [Dim1].[Hierarchy1].CURRENTMEMBER)
, [Measures].[Sales]
)
/
SUM((PARALLELPERIOD([Time].[YM].[Month Name], 11, [Time].[YM].[Month Name].&[201312]):[Time].[YM].[Month Name].&[201312]
, [Dim1].[Hierarchy1].[Level3].&[101])
, [Measures].[Sales]
)
I saw some examples which don't use SUM, but if I remove the SUM operator I'm getting this error:

"The function expects a string or numeric expression for the argument. A tuple set expression was used."

Using SUM will update only one cell in the level4, which is not what I am expecting from this approach.

Can you please help me with it?
Post #1550074
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse