business intelligence consulting
 

Get the last 5 Days, for each Month of the Year using MDX query

by Hans Esquivel 16. July 2013 18:04

In this session I'll show you how to script a query in MDX to get the last 5 days, for each month of a chosen year. After we test the query, we'll validate aggregate values for the first couple of months using a basic MDX query.

 

Step 1 - Define a Calculated Member to Sum and get the Last 5 days

The Aggregate is used to "Sum" the total [Internet Sales Amount] for the last 5 days; the last 5 days is returned by use of the Tail MDX Function. The Tail function returns the subset of days, defined within the Descendants function; the set of days are determined from the current member of the [Date].[Calendar] hierarchy.

WITH MEMBER [Measures].[Sales Last 5 Days of Month] AS

Aggregate(

Tail(

Descendants(

[Date].[Calendar].CurrentMember,

[Date].[Calendar].[Date])

,5)

 ,[Measures].[Internet Sales Amount])

 

Step 2 - Defined the SELECT Statement

The SELECT statement first passes the [Measures].[Sales Last 5 Days of Month] for the column value. Next we use the Filter function to define a set_expression which return a Set for the rows and a logical_expression to evaluate against each tuple in the Set. In this example, we pass [Date].[Calendar].[Month].members for the Filter Set_Expression (this returns the set of members found in the Month level). Our logical_expression uses the Ancestor function to filter by year; in the example, we are filtering all Month's for the Year 2007.

Note: If you need to pass a parameter, you can use the StrToMember function for the passing the Year dynamically. This would be commonly used in reporting in SQL Server Reporting Services, or some other ad-hoc reporting tool. 

 

SELECT {[Measures].[Sales Last 5 Days of Month]} ON 0,

{

Filter([Date].[Calendar].[Month].members,

Ancestor(

[Date].[Calendar].CurrentMember,

[Date].[Calendar].[Calendar Year]) IS

[Date].[Calendar].[Calendar Year].&[2007])

} ON 1

FROM [Adventure Works]

 

Step 3 - Execute the MDX Query

1. Open up  SQL Server Management Studio or MDX Studio and copy and paste the following script into the Query Pane.

2. Run the query. You should get similar results (click image to enlarge). 

 

Step 4 - Validate the values

Run the following query:

 WITH MEMBER [Measures].Jan2007 AS

 Sum(

 {([Date].[Calendar].[Date].&[20070127] :

 [Date].[Calendar].[Date].&[20070131])},        

 [Measures].[Internet Sales Amount]

)

MEMBER [Measures].Feb2007 AS

Sum(

 {([Date].[Calendar].[Date].&[20070224]:

[Date].[Calendar].[Date].&[20070228])},        

[Measures].[Internet Sales Amount]

)

SELECT {[Measures].Jan2007, [Measures].Feb2007} ON 0

FROM [Adventure Works]

You should see the following results. Notice that Jan2007 and Feb2007 are the same as the results found in Step 3.

 

I'm sure there may be alternative ways of doing this, but perhaps this may help you out in the short term. 

 

Cheers!

 

In this session I'll show you how to script a query in MDX to get the last 5 days, for each month of a chosen year. After we test the query, we'll validate aggregate values for the first couple of months using a basic MDX query.

Analysis Services | Analysis Services Tabular Mode | Multidimensional Expressions (MDX)

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud