Get last n year data based on date field in MDX

user_16258129 0
Viewed 40

I want to extract data for last 'N' years based on the Date field. My date field, [OrderDate] is in the below format: [OrderDate] [2007-07-02T00:00:00] I don't have a year hierarchy.

How do I insert the last 'N' logic in below MDX query

SELECT 
{[Measures].[Measure_1],
[Measures].[Measure_2]
      } 
ON 0, 
       NON EMPTY{
[Table].[Customer].[Customer]
*[Table].[Order].[Order]
       }
ON 1 
FROM   [Model]
mdx
1 Answers

You should be able to add a date range into a WHERE clause seperating the two dates using a colon :

SELECT 
{[Measures].[Measure_1],
[Measures].[Measure_2]} 
ON 0, 
NON EMPTY{
[Table].[Customer].[Customer]
*[Table].[Order].[Order]
}
ON 1 
FROM   [Model] 
WHERE {[OrderDate].[OrderDate].[2007-07-02T00:00:00]:[OrderDate].[OrderDate].[2007-07-08T00:00:00]}
user_11798800
Related