February 9, 2015 at 4:26 am
hi all ,
I want to list years has data for all the month[1 to 12]. Exclude the year if
there is no data for any of the month[1 to 12] there is no data.
e.g.
[Month of Year 2011] Measure.val
1 10
2 20
3 30
4 40
5 50
6 60
7 70
8 80
9 90
10 100
11 110
12 120
[Year of 2012] Measure.val
1 10
2 20
3 30
4 NULL
5 50
6 60
7 70
8 NULL
9 90
10 100
11 110
12 NULL
Ans : List on 2011 , Capacity 780
#excluded the year 2012
I am trying around the MDX below please suggest how can i batter achieve it.
WITH
MEMBER
[MEASURES].[Monthly Moving Avg]
AS
'IIF(
[Dim Date].[Calendar Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL =
[Dim Date].[Calendar Hierarchy].[Year].ORDINAL,
AVG(LASTPERIODS (6, [Dim Date].[Calendar Hierarchy].CURRENTMEMBER),
[Measures].[Generation]),
NULL)',
FORMAT_STRING = "$#,##0;0;0;\N\\\A\"
SELECT
{[Measures].[OutPut], [MEASURES].[Monthly Moving Avg]}
ON AXIS (0),
{DESCENDANTS([Dim Date].[Calendar Hierarchy].[Year].allmembers,
[Dim Date].[Calendar Hierarchy].[Year] , self_and_before )}
ON AXIS(1)
FROM
[mycube]
February 12, 2015 at 3:11 am
Here's a way of doing this in AdventureWorks which you can apply to your own data:
WITH MEMBER [MonthsFilled]
AS COUNT(EXISTING([Measures].[Internet Order Count], [Date].[Calendar].[Month]))
MEMBER [InternetFullYearOrders]
AS IIF([MonthsFilled]=12,[Measures].[Internet Order Count], NULL)
SELECT
{[InternetFullYearOrders]} ON 0
,
NON EMPTY
{[Date].[Calendar].[Calendar Year]} ON 1
FROM
[Adventure Works]
Let me explain what it does. Firstly it does a count of the number of months that have internet orders, as we are using year in the query this is broken down by year. Then if there are fewer than 12 months that have orders in a year it does not display anything. Finally a NON EMPTY clause is used in the query to filter out non "full" years.
I hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply