Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    4

    Angry Unanswered: Calculated Measure (Previous Act Value) Problem

    G'Day all,

    I am having trouble with a calculation that on the surface seems to be working until it is opened up in excel.

    The measure calculates the Actual Value as of last year and is as follows - ([Measures].[Act Val],ParallelPeriod([Time].[Year],1))

    This works fine until I look at the totals within EXCEL 2002/2003

    1)If I choose to view a whole year then the total shows me a whole year. That is fine and as expected.

    2)If I choose part of the year the total shows me the whole year. This is wrong and am wondering if it is an Excel probem or my calculation? But here is the weird part -

    3)If I choose another year and select the same months as I have already chosen (ie Year 20023 Jan, Feb and Year 2003 Jan and Feb) then the total for the year that is already selected works!!(shows only the total of the two months - whereas the extra year I have selected (remember - Jan Feb only) shows me a total for the whole year.

    WAIT - There's more

    4)If I add another month to the 2002 selection (ie add March) then the total for 2003 (Jan Feb only) includes the March value also (even though it is not selected)!!!!!!

    Any Ideas??

    Does it make sense??

    Thanks

    Matt

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [scrub]

    Well....

    ParallelPeriod

    Is a udf someone wrote...

    How are you connecting to sql server?

    Are you connecting to sql server?
    [/scrub]


    Sorry...should've looked first....

    I have GOT to start playing with that stuff....never had any real need....
    Last edited by Brett Kaiser; 03-01-04 at 14:21.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, ParallelPeriod is an Analysis Services function. I don't know anything more about it than that.

    From the Holy Book:

    ParallelPeriod
    Returns a member from a prior period in the same relative position as a specified member.

    Syntax
    ParallelPeriod([«Level»[, «Numeric Expression»[, «Member»]]])

    Remarks
    This function is similar to the Cousin function, but is more closely related to time series. It takes the ancestor of «Member» at «Level» (call it ancestor); then it takes the sibling of ancestor that lags by «Numeric Expression», and returns the parallel period of «Member» among the descendants of that sibling.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2002
    Posts
    4

    Smile

    You should it's a powerful addition to SQL Server - only sometimes you can get lost in translation (so to speak) - the MDX statements (language for writing the calcs) sometimes take a while to understand. Hopefully you take a crash course and help me with my problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •