Friday, February 24, 2012

Help with Variance in MDX Query

I have the following MDX query. It calculates my measure (Call Count) by County on two different days. I would like my query to only return counties where the difference between day 1 and day 2 is greater than 10% of day 1. In other words I am looking for a variance of greater than 10%

select ({[Date].[Date].&[20060901], [Date].[Date].&[20060908]}, {[Measures].[Call Count]}) on columns,
[NPA NXX].[By Zip Code].[County].Members on rows
from [CallStats]

Thanks,
-Darrell

You need to use the filter function to filter your counties. Here's an example query in Adventure Works which does roughly the same thing, ie returns all products where Internet Sales Amount is 10% greater in the second of the two dates displayed:

select
[Date].[Calendar].[Calendar Semester].&[2004]&[1].children
*
[Measures].[Internet Sales Amount]
on 0,
non empty
filter(
[Product].[Product].[Product].members,
(([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Quarter].&[2004]&[2])
-
([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Quarter].&[2004]&[1]))
/
([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Quarter].&[2004]&[1])
>.1)
on 1
from
[Adventure Works]

HTH,

Chris

No comments:

Post a Comment