CODEWITHSUNDEEP
sql-serverssasmdx
BarrieK
BarrieK

Reputation: 1269

MDX query to get distinct TOP N from sub-level?

I am struggling to get an MDX query to select the TOP N from a sub-level.

In the following dataset, I'm trying to get the Salesman and top Sales Amount each day (highlighted) - note that "Ben" does not have any sales data until the last day: -

Data

So what I'd Like to see is this: -

Correct Results

I looked at this post "Generate, TopCount, and All Others" and created this query: -

WITH
SET [DateTime2Set] AS ([Time].[Day].&[20171101]:[Time].[Day].&[20171106])

SET [Sales Guys] AS [Sales Persons].MEMBERS

SET [TopSales] AS
GENERATE(
    DateTime2Set,
    TopCount(
        EXISTING [Sales Guys],
        1,
        [Sales Amount]
    )
)

SELECT 
{
    [Sales Amount]
}
ON 0,

{
NonEmpty(
    (
        [DateTime2Set],
        [TopSales]
    )
    ,[Sales Amount]
)
}

 ON 1

FROM [Cube]

When I run the query I get the following results, with an unexpected item (highlighted)

enter image description here

So what is happening is that the top items per day are being added to an overall set which is then being joined onto the days.

This is not what I require. I tried adding the crossjoin as described in the MSDN documentation for the MDX Generate function like this: -

SET [TopSales] AS
GENERATE(
    DateTime2Set,
    TopCount(
        [Time].[Day].CURRENTMEMBER * [Sales Guys],
        1,
        [Sales Amount]
    )
)

But just get the error:-

The Day hierarchy is used more than once in the Crossjoin function

How can I change the query to get the results that I need?

Upvotes: 0

Views: 205

Answers (1)

BarrieK
BarrieK

Reputation: 1269

OK, I worked it out finally.

SET [TopSales] AS
GENERATE(
    DateTime2Set,
    TopCount(
        DateTime2Set.CURRENTMEMBER * EXISTING Tags,
        1,
        [Sales Amount]
    )
)

SELECT 
{
    [Sales Amount]
}
ON 0,

{
NonEmpty(
    (
        [TopSales]
    )
    ,[Sales Amount]
)
}

ON 1

FROM [EventsCube]

I just removed DateTime2Set from the Columns and crossjoined DateTime2Set in the [Top Sales] calculated member

Upvotes: 1

Related Questions