locked
Sorting Order Desc by Date with Amount not working. RRS feed

  • Question

  • Hi,

    I have a MDX query, that should sort by desc date and provide the measure. However, it is not sorting descendingly. In fact, the date row seems not to sort at all.

    Can someone please tell me if the MDX will work? If not, please correct it. If it is suppose to work, why isnt the date field sort not working? Any suggestions? Is it a processing issue, where I need to set the field to a dateformat?

    Thanks.
    Nijojo



    select
    [Measures].[Amount] on columns,

    non empty order([Dim Date Period].[Month Date].members,[Measures].[Amount],desc) on rows

    from [Database]

    where [Dim Date Period].[Period_Start_Date].&[2008-07-01T00:00:00] : [Dim Date Period].[Period_Start_Date].&[2009-07-01T00:00:00]

    Tuesday, March 10, 2009 4:53 AM

Answers

  • Hi Nijojo,


    If I understand correctly you would like to order based on the dates and not based on the measure. Your example will shoe the highest measure value first.

    If you like to order based on the date try following query

    select [Measures].[Amount] on columns,
    non
    empty order([Dim Date Period].[Month Date].members,[Dim Date Period].[Month Date].membervalue,desc) on rows
    from
    [Database]
    where [Dim Date Period].[Period_Start_Date].&[2008-07-01T00:00:00] : [Dim Date Period].[Period_Start_Date].&[2009-07-01T00:00:00]


    I hope this helps.
    Regards,
    Orsi

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 10, 2009 8:10 AM
    Answerer

All replies

  • Try using BDESC instead of DESC.
    ..hegde
    Tuesday, March 10, 2009 8:03 AM
  • Hi Nijojo,


    If I understand correctly you would like to order based on the dates and not based on the measure. Your example will shoe the highest measure value first.

    If you like to order based on the date try following query

    select [Measures].[Amount] on columns,
    non
    empty order([Dim Date Period].[Month Date].members,[Dim Date Period].[Month Date].membervalue,desc) on rows
    from
    [Database]
    where [Dim Date Period].[Period_Start_Date].&[2008-07-01T00:00:00] : [Dim Date Period].[Period_Start_Date].&[2009-07-01T00:00:00]


    I hope this helps.
    Regards,
    Orsi

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 10, 2009 8:10 AM
    Answerer
  • Oh I overlooked the problem statement, ignore my comment since you are looking for date based sorting and just show measure.. I'm with what Orsi has suggested.
    ..hegde
    Tuesday, March 10, 2009 8:58 AM