-
MDX Numeric Functions: The Min() Function
Hi to all, I'm new in this mdx world!
I'm trying to create a query in adventures work cube, that returns a table with the MAX and MIN value of Reseller Sales Amount by Reseller, but I also want as measures in the table, which month is linked to every MAX and MIN value.
I already have (following http://www.databasejournal.com/featu...x-Function.htm), the MAX and MIN value, and also the Month linked to the MAX, but it's impossible for me to get the Month linked to the MIN!
Right now I have this:
WITH
MEMBER [Measures].[Peak Sales]
AS MAX(DESCENDANTS( [Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month]),[Measures].[Reseller Sales Amount])
MEMBER [Measures].[Peak Period]
AS IIF(MAX([Date].[Calendar].[Month].MEMBERS, [Measures].[Reseller Sales Amount]) > 0,
TOPCOUNT(DESCENDANTS( [Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month]), 1, [Measures].[Reseller Sales Amount]).ITEM(0).NAME, null)
SELECT {[Measures].[Peak Sales], [Measures].[Peak Period]} ON AXIS(0),
NON EMPTY {[Reseller].[Reseller Type].[Reseller].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
The code you find in red, is the want that I need to adapt to get the MIN peak month... how can I write it?
Many thanks in advance
Kind regards
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|