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 Sum Over multi selection in a time dimension Expand / Collapse
Author
Message
Posted Sunday, March 30, 2014 5:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 88, Visits: 180
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]
HIERARCHY [DataAAAAMMGG]
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)

AND

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 days...as described before.

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

'SUM
(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.

Regards.








Post #1556304
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse