Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

MDX Sum Over multi selection in a time dimension Expand / Collapse
Posted Sunday, March 30, 2014 5:01 PM


Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 101, Visits: 234
Good evening to all.

I try to have an help it is possibile
My name is Mauro i am writing from Italy...and i am little new about MDX SSAS 2008 R2.

I have a SALES Cube that contains a [Measures].[Sales Amount] and a time dimension [Calendar].

Time dimension CALENDAR is like this :

DIMENSION [Calendar]
First level of Hierarchy [Year]
Second level of hierarchy [Month]
Third Level of hierarchy [Day]

I use Excel 2013 to browse SALES Cube.

I select Calendar Dimension in a multi value mode .

That is:

1) I both select Year=2014 Month = 3 Days from 1 to 28 (i want sales amount of 1-28 OF march 2014)


2) I both select Year=2014 Month = 3 Days from 2 to 29 (i want sales amount of 1-28 OF march 2014

So i choose different periods in time dimension , and i want to calculate difference of sales between first selection 1) and second selection 2)

Example: (Sales 2014-3- from 1 to 28 ) - (Sales 2013-3 From 2 to 29)

I do not know if i did good to use parallel period...

I tried but i do not what is the best to do that...

If i drag [Measure].[Sales] it works but i need to calculate a delta from sales of different years , same month , and different range of described before.

I wrote a calculate member but it calculate very wrong values...:

(Existing [Calendar].[DataAAAAMMGG].[Month].Members,
([Measures].[Sales] - (ParallelPeriod([Calendar].[DataAAAAMMGG].[Year], 1), [Measures].[Sales])))'

I hope someone help me...
I am sure someone did something of similar in his own cubes..

To be clear if Sales Amount of 2014-3- from 1 to 28 = 100.000 euro and Sales Amount of 2013-3- from 2 to 29 = 80.000

i need to have difference 100.000 - 80.000 = 20.000

ps. Notice i select multivalues in time dimension and not the same...that is days range different (1-28 and 2-29)

I hope you can help me thanks.


Post #1556304
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse