SSAS FAQ MDX FAQ MDX query to get sales by prod line for period plus number of months with NON EMPTY sales?
MDX query to get sales by prod line for period plus number of months with NON EMPTY sales?
User Rating: / 0
PoorBest 
Written by Vidas Matelis   
Monday, 30 April 2007 19:32

Q: MDX query to get sales by product line for specific period plus number of months with non empty sales.

A: You can use COUNT() function with ExcludeEmpty option. For count function you specify set that is corssjoin of Date members at the month level and measure that you are interested in.

WITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Sales Amount]
, ExcludeEmpty
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

Results will be:

Sales Amount Months With Above Zero Sales
Accessory $1,987,396.37 19
Components $454,644.34 18
Mountain $24,430,307.51 19
Road $24,919,506.74 19
Touring $16,010,837.10 13

< Prev   Next >
 
Strategy Companion