Post reply

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

  • 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!

  • November 11, 2011 at 8:24 pm

    #1406630

    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.

  • December 14, 2011 at 5:21 am

    #1421180

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

  • December 16, 2011 at 6:28 am

    #1422452

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

  • December 16, 2011 at 6:37 am

    #1422463

    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.

  • December 19, 2011 at 2:54 am

    #1423001

    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

  • December 23, 2011 at 8:13 am

    #1425189

    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....

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

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