MDX Calculation based on 2 dates for a calculation in a cube

Question

MDX Calculation based on 2 dates for a calculation in a cube

vee van gelder

Ten Centuries

Points: 1337
More actions
November 10, 2011 at 12:30 am

#262528

hi there
I am wanting to do a calculation in the cube to get my Net Sales Value.
How i do that is calculate my dispatches less goods returned on any given day.
If i had to write this in t-sql it would look something like this
select value as despatchedvalue from table where despatchdate =(for eg.) '15 may 2011' and orderstatus = 'dispatched'
select value as goods returned value from table where goods returneddate = (for eg.) '15 may 2011' and orderstatus = 'good returned'
select despatchvalue - goods returned value = net sales value
Currently i have my dispatches less my goods returned but its not date specific i.e
sum([Order Status Bucket].[Order Status].&[Despatched],[Measures].[Value])- sum([Order Status Bucket].[Order Status].&[Goods Returned],[Measures].[Value])
how do i add my dates in there so that its looking at for example all dispatches on may 15 - all goods returned on may 15?
tia!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mark 38539 SSC Eights! Points: 848 More actions · Answer 1

Can you post your exact MDX query? I am thinking you don't need to use sum function, but I need more data about your cube.

vee van gelder Ten Centuries Points: 1337 More actions · Answer 2

i dont have the query and thats the issue, i dont know how it write it.

kranthi_kk1 Valued Member Points: 68 More actions · Answer 3

can you briefly describe the structures of you dimensions,facts and attributes .

mark 38539 SSC Eights! Points: 848 More actions · Answer 4

It is hard to answer without more information, but just to try to point you in the right direction, you can add a where to your query to select a slice of the cube, or you can use a subselect to restrict the subspace.

vee van gelder Ten Centuries Points: 1337 More actions · Answer 5

hi all

thanks for trying to assist me

this is what i currently have

this is the current code

([Depatch Date].[Despatch Date].Currentmember,[Measures].[Value])- (LinkMember([Depatch Date].[Despatch Date].Currentmember,[Goods Returned Date].[Goods Returned Date].[Date]),[Measures].[Value])

but i get this error though;

the linkmember function expects a hierarchy expression for the 2 argument, a member expression was used

i hope this makes sense

Rakesh-439259 SSC Enthusiast Points: 131 More actions · Answer 6

If you are trying to build a calculated member, you just have to write the expression as

([Measures].[Sales],[Good Dispatched] - [Measures].[Sales],[Goods returned])

(Please add appropriate expression for the dimension)

Build the cube and browse this new calculated member by date dimension. You will notice that this measure gives the correct result.

If you are using this even in a SSRS report, the same calculation would work, you will have to slice the measure by dates thats all....