MDX Calculation

Question

Hi There,

I have Date dimension which has (Year - Quarter - Month-Day) DimDate.YQMD, in my fact table, I have OrderDate, and No of order (OrdCt), I also have DeliverDate and No of deliver (DeliverCt). We want to caculate the ration like this, it is March now, the ration is: sum(DeliverCt from Feb, Jan, Dec of last year)/ sum (OrdCt from Jan, Dec, Nov of last year).

esstentially (DeliverCt of previous three month of current month -1)/(OrdCt of previous three month of current month-2)

What function I need to use in MDX?

Many thanks

---

Don

Answer 1

any pointer/advice is appreciated

---

Don

Answer 2

Hi,

I think you can use

SUM(ParallelPeriod(MTD(),3)

and then compare values check this link it just something illustarte how to compare:

http://dataqueen.unlimitedviz.com/2012/02/compare-week-last-year-for-a-53-week-year-using-mdx/

Or you may create parameter to get your current (user entred) month and yous function methods to get previous (2) months.

WITH 
MEMBER [Measures].[Current] AS
(
(
[Calendar].[Year Calendar].[Month].[@param],
[Measures].[sales]
)
+
(
[Calendar].[Year Calendar].[Month].[@param], --Use function like this to get previous month DateAdd("m", -1, Date)
[Measures].[sales]
)
+
(
[Calendar].[Year Calendar].[Month].[@param], -- use function like this to get month -2 DateAdd("m", -1, Date)
[Measures].[sales]

)
)

then create another measure for the other 3 months and after that create measure to get the percent.
Good Luck

I hope this is helpful

Elmozamil Elamir

MyBlog

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid
http://elmozamil.blogspot.com

Answer 3

Elmozamil,

Thanks for pointer. I have to handle this in Caculation, I come up with similar thing as you suggested:

sum({[Date].[YQMD].prevmember:[Dim Date].[YQMD].prevmember.lag(2)},[Measures].[DeliverCt]),

This gave me total deliver order count from Feb, Jan and Dec (2011), for example, since current month is March.

However I need my total order count from Dec, Nov and Oct (2011), two month before deliver order, how do I say in my MDX?

Thanks again

---

Don

Answer 4

If any given time, your calculation has to go two months back from the current-month then You might use this -

sum({[Date].[YQMD].currentmember.lag(3):[Dim Date].[YQMD].currentmember.lag(5)},[Measures].[DeliverCt])

here is the equivalent to Adventureworks :

WITH MEMBER TestMeasure AS
SUM
([Delivery Date].[Calendar].currentmember.lag(3):[Delivery Date].[Calendar].currentmember.lag(5),Measures.[Internet Sales Amount] )

SELECT {Measures.[Internet Sales Amount],Measures.[TestMeasure]} On 0,
[Delivery Date].[Calendar].[Month].members ON 1
FROM [Adventure Works]

---

Hope this Helps!

Answer 5

Sorry to cut in. On the surface, the question looks like a simple one but it's quite a bit more complex than what it appears to be on the surface. Like SSASBeginner, I'll use AdventureWorks to illustrate. To generalize so that you can incorporate this calculation into the Cube editor script, you'd need three role playing date dimensions, which thankfully, the AdventureWorks sample has. Say I select [Jul, 2007] from the Date dimension. I want to calculate the ratio of Internet Sales based on Ship Date from Mar through May to the Internet Sales based on Delivery Date from Apr to Jun. This is similar to the requirements you've described if I've understood your problem correctly. To do so, I can use the LinkMember function to determine the Ship Date member that would correspond to the CurrentMember selected in the Date dimension, LinkMember( [Date].[Calendar].CurrentMember, [Ship Date].[Calendar] ). Also do the same for the [Delivery Date] member. To calculate the total Internet Sales over the three month range, the range operator can be used to select the three month range but the current context of the [Date].[Calendar] context must be overriden by aggregation over the ([Measures].[Internet Sales Amount], [Date].[Calendar].[All]) tuple rather than just the [Measures].[Internet Sales Amount] member. The final query will look something like the following.

WITH MEMBER [Measures].[Amount By Ship Date] AS
	SUM(
		{ LinkMember( [Date].[Calendar].CurrentMember, [Ship Date].[Calendar] ).Lag(4) : LinkMember( [Date].[Calendar].CurrentMember, [Ship Date].[Calendar] ).Lag(2) },
		( [Measures].[Internet Sales Amount], [Date].[Calendar].[All] ) 
	), FORMAT_STRING="Currency"
MEMBER [Measures].[Amount By Delivery Date] AS
	SUM(
		{ LinkMember( [Date].[Calendar].CurrentMember, [Delivery Date].[Calendar] ).Lag(3) : LinkMember( [Date].[Calendar].CurrentMember, [Delivery Date].[Calendar] ).Lag(1) },
		( [Measures].[Internet Sales Amount], [Date].[Calendar].[All] )
	), FORMAT_STRING="Currency"
MEMBER [Measures].[Shipped over Delivered Ratio] AS
	[Measures].[Amount By Ship Date] / [Measures].[Amount By Delivery Date],
	FORMAT_STRING="Percent"
SELECT	{
		[Measures].[Amount By Ship Date],
		[Measures].[Amount By Delivery Date],
		[Measures].[Shipped Over Delivered Ratio]
	} ON COLUMNS
FROM	[Adventure Works]
WHERE	(
		[Date].[Calendar].[Month].&[2007]&[7]
	)

Then you can migrate the calculated members to the Calculations tab of the cube editor if it needs to be referenced by other reports, etc.

HTH, Martin

---

http://martinmason.wordpress.com

Answer 6

Thanks Martin for the elaborated answer!

---

Hope this Helps!

Answer 7

Martin, SSASBeginner,

Thank you so much for advice, I am truly beginner, eventually I decide to simplify the scenario, I am going to use one date (so no role playing date needed),  I create calculated member in the cube, here is what I came up with this, it seems working, but I am not sure so please point it out if it is not right.

I then try to create reporting service report using cube as my data source, the calculated member (DeliverOrderRatio) is one of the measure in my report, the report I have year, month as report parameter, so user will pick year and month. My report will show sales branches at rows, all the measures in columns, but the following calculated member will not work since Date.YQMD.Year and Date.YQMD.Month is at parameter.

prevmember.lag(2)},[Measures].[DeliverCt])=0 then null else
sum({[Date].[YQMD].prevmember:[Date].[YQMD].prevmember.lag(2)},[Measures].[DeliverCt])/
sum({[Date].[YQMD].prevmember.lag(1):[Date].[YQMD].prevmember.lag(3)},[Measures].[OrderCt]) end

Here is report layout:

Year drop down, Month drop down

                                 OrderCt    DeliverCt    DeliverOrderRatio

Sales Branch

     Sales Office

          Sales Person

Do I write the script in the efficient way? Is there way this can work with only sales branch dimension? Any more advice on this?

---

Don

Answer 8

Hi Don,

You're really close. Try reversing the two parameters of the range operator so they're earliest member is specified first. I believe it will work if you do so. Generally, when working with MDX, I try to get a query scoped calculation working first before migrating the calculation to the calculations tab of the cube editor. Using AdventureWorks as an example, you're query should look something like the following:

WITH MEMBER [Measures].[Amount By Ship Date] AS
	SUM(
		{ [Date].[Calendar].PrevMember.Lag(3) : [Date].[Calendar].PrevMember.Lag(1) },
		( [Measures].[Internet Sales Amount] ) 
	), FORMAT_STRING="Currency"
MEMBER [Measures].[Amount By Delivery Date] AS
	SUM(
		{ [Date].[Calendar].PrevMember.Lag(2) : [Date].[Calendar].PrevMember },
		( [Measures].[Internet Sales Amount] )
	), FORMAT_STRING="Currency"
MEMBER [Measures].[Shipped over Delivered Ratio] AS
	[Measures].[Amount By Ship Date] / [Measures].[Amount By Delivery Date],
	FORMAT_STRING="Percent"
SELECT	{
		[Measures].[Amount By Ship Date],
		[Measures].[Amount By Delivery Date],
		[Measures].[Shipped Over Delivered Ratio]
	} ON COLUMNS
FROM	[Adventure Works]
WHERE	(
		StrToMember("[Date].[Calendar].&[2007]&[7]", CONSTRAINED)
	)

HTH, Martin

---

http://martinmason.wordpress.com