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

Struggling Creating Calculated Measure Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 1:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 9:20 AM
Points: 6, Visits: 30
I’m struggling implementing a calculated measure in one of my cubes. Basically I need to take two measures, divide them for a given period, and them sum the results together. For example, if the user selects 2012 as the year, I need to do the following:

(Q1, Q2, etc. are children under 2012 in my date.calendar dimension hierarchy)

(Q1 Purchases / Q1 Base) +
(Q2 Purchases / Q2 Base) +
(Q3 Purchases / Q3 Base) +
(Q4 Purchases / Q4 Base) +

But this needs to work dynamically based on whether the user selects multiple years, a single year, a single quarter, independent quarters for any given years, etc. Anyone have any ideas?


Thanks in advance,
Jason
Post #1448955
Posted Friday, May 3, 2013 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:14 AM
Points: 4, Visits: 31
Replace [Internet Order Count] with base
and [Measures].[Customer Count] with purchases
from the below example.

I have created a [hidden measure] which calculates the purchase/base for every date member.
The [RequiredMeasure] with sum the children which will result in the calculation like you requested.

CREATE MEMBER CURRENTCUBE.[Measures].[HiddenMeasure]
AS null,
VISIBLE = 1 ;
SCOPE([Date].[Calendar].MEMBERS ,[Measures].[HiddenMeasure]);
THIS=iif([Measures].[Internet Order Count]=0,NULL,[Measures].[Customer Count]/[Measures].[Internet Order Count]) ;
END SCOPE;


CREATE MEMBER CURRENTCUBE.[Measures].[RequiredMeasure]
AS null,
VISIBLE = 0 ;
SCOPE([Date].[Calendar].MEMBERS ,[Measures].[RequiredMeasure]);
THIS=sum([Date].[Calendar].currentmember.children,[Measures].[HiddenMeasure] );
END SCOPE;

Hope it helps
Post #1449252
Posted Friday, May 3, 2013 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 9:20 AM
Points: 6, Visits: 30
moviesvj, thanks for the reply. Works great!
Post #1449254
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse