SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDX Sum Over multi selection in a time dimension


MDX Sum Over multi selection in a time dimension

Author
Message
maretix
maretix
SSC Eights!
SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)

Group: General Forum Members
Points: 827 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]
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search