Actually I am trying to make a correlation matrix oF products dimension,

thats easy , but problem arises when I want to see it quarter wise and

the time break for correlation calculation is that quarter's months.

Consider 2 dimensions



TIME

PRODUCT



I made another dimension for product called PRODUCT2



Now I executed the following query to accomplish the below report.





WITH MEMBER [Measures].[CR] AS

'Correlation([TIME].CURRENTMEMBER.CHILDREN,

(Product.currentmember, product2.[all product2], measuers.sales),

(Product2.currentmember, product.[all product], measures.sales))'

SELECT

PRODUCT.CATEGORY.MEMBERS ON COLUMNS,

CROSSJOIN(PRODUCT2.CATEGORY.MEMBERS,

[TIME].[QUARTER].MEMBERS) ON ROWS

FROM SALES

WHERE [MEASURES].[CR]







--------P1----------P2 -------- P3

P1 Q1 1 ---------- .8---------- .5

----Q2 1 ----------.3---------- .2

----Q3 1 ----------.1 ---------.65

----Q4 1 ----------.23 ----------.25



P2 Q1 .8---------- 1 ----------.51

----Q2 .3 ----------1 .----------22

----Q3 .1 ----------1 ----------.75

----Q4 .23 ---------1 .----------85



P3 -Q1 .5 ----------.51---------- 1

----Q2 .2---------- .22 ----------1

----Q3 .65---------- .75 ----------1

----Q4 .25---------- .85---------- 1





The query is executing without error, but the result contains

correlations like 3.45, 2.34, -2.73 etc.



According to my knowledge correlations is between -1 and 1. it can

never be less than -1 or greater than 1.



How can I achieve this, I also tried by using TIME2 dimension

and LINKMEMBER but then it gives and error that

"the level name is not unique :month"



I hope you understand the problem, please reply urgently as this

is my assignment in my office and i am stuck with it



Thanks

Asim Naveed


--
Posted via http://dbforums.com