Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

mdx calculated measure issue Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 7:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:11 AM
Points: 78, Visits: 233

Hi everyone,

I have wriiten 2 calculated measures for my cube..
my cal measures are:

create member [Measures].[A]
as
iif([measures].[sales]=0,NULL,
[measures].[Latencydays]/[measures].[sales]),
visible=1

create member [Measures].
as
iif([measures].[Responses]=0,NULL,
[measures].[Latencydays]/[measures].[Responses]),
visible=1

but results are different from database to cube..
those are...

[b]below result is from DB:

avgsaleslatency avgResplatency latency days sales responses
Null Null 0 Null Null
15 Null 15 1 Null
Null 23 23 Null 1


cube result

latencydays sales responses avgsaleslatency avgresplatency
38 1 1 38 38

but as aggregate is sum for latencydays it showing the results as below:

But Here what the expected result is :

avgsaleslatency avgresponseLatency
15 23


can anybody correct me, if my measure are wrong or if there is any other way to fix it..



Thanks in advance,
Niharika
Post #1472835
Posted Friday, July 12, 2013 12:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 451, Visits: 847
Hi,
It's a little hard to tell without knowing your data structure but it seems as if your measure called "LatencyDays" needs to be split via a dimension.
It looks as if what your cube is reporting is correct in that it's summing up ALL of your latency days (15+23=38) without the context of a dimensional member to slice it by.

So, say you had a dimension that was joined to whatever measure group your LatencyDays measure is in, let's call it LatencyType and assume it has one attribute called LatencyType. Then your calculated measure would look like this:
MEMBER [Measures].[SalesLatency]
AS
SUM([LatencyType].[LatencyType].&[Sales], [Measures].[LatencyDays])

Hope this helps





I'm on LinkedIn
Post #1472872
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse