Second MAX through MDX

Question

I can get the MAX() of some Measure based on Dimensions specified. I have no issues with that. What I am looking for is Second MAX(). Means if I sort the some Measures in DESC order it is 5,4,3,2,1

With MAX() I can get 5 but I want 4. How do I compute that?

Answer 1

Use the EXCEPT keyword to knock out the top one from the top two set:

SELECT
    [Measures].[YourMeasure]
ON 0,
NON EMPTY
{
    EXCEPT
    (
        TOPCOUNT( [Date].[Date].[Date], 2, [Measures].[YourMeasure] ),
        TOPCOUNT( [Date].[Date].[Date], 1, [Measures].[YourMeasure] )
    )
}
ON 1
FROM [YourCube]