Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Error with range : when used with Existing function

Answered Error with range : when used with Existing function

  • Tuesday, January 29, 2013 6:12 PM
     
      Has Code
    Sign In to Vote
    0

    Can anyone please tell me what's wrong with the following query-

    "The funtion : expects a member expression for 2 argument. A tuple set expression was used."

    WITH
MEMBER [measures].[last120days] AS
AGGREGATE({Null : existing([ACTIONDATE].[ACTIONDATE].Currentmember).Lag(4)},[Measures].[RecordCount])
select [measures].[last120days] on 0,
[ACTIONDATE].[ActionDate].[Month].&[2013 - January] on 1
from 
[CUBE]

    THANKS!

    -Julie

     

All Replies

  • Tuesday, January 29, 2013 9:21 PM
    Answerer
     
     Answered Has Code
    Sign In to Vote
    1

    Hi,

    first of all EXISTING is a keyword and not a function therefore you do not need any brackets

    EXISTING is used to evaluate a set in the current context, you use "[ActionDate].[ActionDate].currentmember" as your Set what does not make much sense as it is only one member

    if you use NULL as the first argument of your range-operator ":" then you will aggregate values from the beginning of time till the second parameter and not "last120days"

    you may try this query:

    WITH
MEMBER [measures].[last120days] AS
AGGREGATE(LastPeriods(4, [ACTIONDATE].[ACTIONDATE].Currentmember), [Measures].[RecordCount])
select [measures].[last120days] on 0,
[ACTIONDATE].[ActionDate].[Month].&[2013 - January] on 1
from [CUBE]

    hth,
    gerhard

    - www.pmOne.com -

    • Marked As Answer by Julie Chang Wednesday, January 30, 2013 3:41 PM
    •