How do I build a MDX query that considers only facts that happened in the last 10 days of February?

1
votes

I have a fact table that has a time dimension, which contains year, month, day and hour.

I was able to find ways to filter things that happened in a given day, or month (simple where/filter by the desired level). But I would like to create an MDX query that filter the results so my cube has information about the facts recorded in the last 10 days of febraury.

Is there anyway I can do it?

2
mdxolapolap-cubemondrian

2 Answers

3
votes

Assuming you have all the days of February in your cube, you could use a set inside there WHERE clause.

Something like this..

WHERE ([Date].Month)

Supposing you have a Year-Month-Day-Hour hierarchy in place and there may be some dates missing

Select....... on COLUMN,
....... ON ROWS
FROM ....
WHERE
({[Time].[Month].&[Feb 2015].LastChild.LAG(10) : [Date].[Month].&[Feb 2015].LastChild})

If no dates are missing in the date dim,

select ... ON COLUMNS,
... ON ROWS
FROM ...
WHERE
({[Time].[Date].&[02/19/2015] : [Date].[Date].&[02/28/2015]})

If you want the sales for last 10 days of Feb for every year:

SELECT Measures.Sales ON COLUMNS,
       Products.Products.MEMBERS ON ROWS
FROM 
( 
    SELECT
    generate //This would build the set for the last 10 days of Feb for every year
            (
             [Time].[Year].[All].children, 
             TAIL //This returns the last 10 days of february(second month)
                (
                 [Time].[Year].CURRENTMEMBER.FIRSTCHILD.LEAD(1).CHILDREN, 
                 10
                )
            ) ON COLUMNS
    FROM YourCube
)
1
votes

Just as some extra info - if you want a "rolling" 10 day sum or 10 day average then code similar to the following is a possible approach:

WITH 
  MEMBER [Measures].[Sum 10] AS 
    Sum
    (
      LastPeriods
      (10
       ,[Date].[Calendar].CurrentMember
      )
     ,[Measures].[Internet Order Count]
    ) 
  MEMBER [Measures].[MovAvg 10] AS 
    Avg
    (
      LastPeriods
      (10
       ,[Date].[Date].CurrentMember
      )
     ,[Measures].[Internet Order Count]
    ), format_string = "#.000" 
SELECT 
  {
    [Measures].[Internet Order Count]
   ,[Measures].[Sum 10]
   ,[Measures].[MovAvg 10]
  } ON 0
 ,Descendants
  (
    [Date].[Calendar].[Month].&[2006]&[2]
   ,[Date].[Calendar].[Date]
  ) ON 1
FROM [Adventure Works];

It returns data like the following:

enter image description here