INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Calculated member and ParallelPeriod

Calculated member and ParallelPeriod

(OP)
I need to calculate a percentage movement between 2 periods. I'm trying to use the following code:

WITH MEMBER [Measures].[Gross Sales Weekly % Movement]
 AS ({[Calendar].[YMWD].[Week No], [Measures].[Gross Sales]}
-
{ PARALLELPERIOD( [Calendar].[YMWD].[Week No],1,[Calendar].[YMWD].[Week No].currentmember), [Measures].[Gross Sales]})
 /
{ PARALLELPERIOD( [Calendar].[YMWD].[Week No],1,[Calendar].[YMWD].[Week No].currentmember), [Measures].[Gross Sales]}

I keep getting error messages and no matter what variations on a theme I try I can't get it right.

Should I be using parallelperiod at all?

Any help greatly appreciated.

'The world isn't round - it's bent!'   Spike Milligan

RE: Calculated member and ParallelPeriod

Try this:

CODE

WITH MEMBER [Measures].[Gross Sales Weekly % Movement] AS
(([Calendar].[YMWD].CURRENTMEMBER, [Measures].[Gross Sales])
-
(PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales]))
/
(PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales])
, FORMAT_STRING = "Percent"

RE: Calculated member and ParallelPeriod

(OP)
Thanks.

Using the following code to test it before putting it in the Calculation script:

WITH MEMBER [Measures].[Gross Sales Weekly % Movement] AS
(([Calendar].[YMWD].CURRENTMEMBER, [Measures].[Gross Sales])
-
(PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales]))
/
(PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales])
, FORMAT_STRING = "Percent"

select  [Calendar].[YMWD].[Week No] * [Measures].[Gross Sales Weekly % Movement] on 0,
[Items].[Item Number].[Item Number] on 1
from [Sales Statistics]


I get 1.#INF or -1.#IND for all the values (other than NULL). MDX isn't my strong suite but I think the query is ok. But my MDX knowledge is not enough to see what, if anything, is the issue with the calculation.

Is the problem with the member definition or my query?

'The world isn't round - it's bent!'   Spike Milligan

RE: Calculated member and ParallelPeriod

A lack of data.  If you have an #INF, your previous week's value is probably NULL.  If you have an #IND, your previous week's value is probably 0.  

Try this out, and see if you can make sense of it.  Let me know if you see anything with a Gross Sales value.  If you do, and there is a value for Gross Sales for the week before, you should get a percentage figure.

CODE

WITH MEMBER [Measures].[Gross Sales Weekly % Movement] AS
(([Calendar].[YMWD].CURRENTMEMBER, [Measures].[Gross Sales])
-
(PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales]))
/
(PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales])
, FORMAT_STRING = "Percent"

select  [Calendar].[YMWD].[Week No] * {[Measures].[Gross Sales Weekly % Movement], [Measures].[Gross Sales]} on 0,
[Items].[Item Number].[Item Number] on 1
from [Sales Statistics]

RE: Calculated member and ParallelPeriod

(OP)
Thanks, that showed me how the data is. You are right, the data is sparse and I get lots of nulls and the error codes appear in weeks with data following weeks with null entries. (Although other queries via SSAS cube browser or Excel show more data than is shown by the query - weird).

Is it possible to replace the error codes with something else, like '0'? I can't see the accountants liking the error codes.

Also, presumably, because I've used a hierarchy in the WITH MEMBER definition, this calculation will only work when that hierarchy is used as a dimension in the report?

Thanks.

'The world isn't round - it's bent!'   Spike Milligan

RE: Calculated member and ParallelPeriod

You could use something like this:

CODE

WITH MEMBER [Measures].[Gross Sales Weekly % Movement] AS
CASE
    WHEN ISEMPTY((PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales])) = TRUE THEN 0
    WHEN (PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales]) = 0 THEN 0
    ELSE
    (([Calendar].[YMWD].CURRENTMEMBER, [Measures].[Gross Sales])
    -
    (PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales]))
    /
    (PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER), [Measures].[Gross Sales])
END
, FORMAT_STRING = "Percent"

select  [Calendar].[YMWD].[Week No] * {[Measures].[Gross Sales Weekly % Movement], [Measures].[Gross Sales]} on 0,
[Items].[Item Number].[Item Number] on 1
from [Sales Statistics]

Quote:


Also, presumably, because I've used a hierarchy in the WITH MEMBER definition, this calculation will only work when that hierarchy is used as a dimension in the report?

Yes, but that completely makes sense.  If you call something "Gross Sales Weekly % Movement," you obviously want to use it in conjunction with your calendar hierarchy.  

RE: Calculated member and ParallelPeriod

(OP)
Great. That looks better. I just need to find out why I get different results when I browse using the same members using an Excel pivot table or the cube browser in SSAS on the one hand, and this query on the other.

Thanks,

'The world isn't round - it's bent!'   Spike Milligan

RE: Calculated member and ParallelPeriod

Excel doesn't always submit what you would think of as being the correct MDX to the SSAS database.  You might find this utility useful, it's called OLAP Pivot Table Extensions, and it allows you to right-click on your pivot table, and take a look at the MDX Excel is submitting.

http://olappivottableextend.codeplex.com/

RE: Calculated member and ParallelPeriod

(OP)
Nice tool. Thanks for that.

Have another star.

'The world isn't round - it's bent!'   Spike Milligan

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close