Calculated member and ParallelPeriod 1

[Flybridge]

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

 

[RiverGuy]

Try this:

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"

 

[Flybridge]

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

 

[RiverGuy]

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.

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], [red][Measures].[Gross Sales][/red]} on 0,
[Items].[Item Number].[Item Number] on 1
from [Sales Statistics]

 

[Flybridge]

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

 

[RiverGuy]

You could use something like this:

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]

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.

 

[Flybridge]

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

 

[RiverGuy]

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/

 

[Flybridge]

Nice tool. Thanks for that.

Have another star.

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