How to use greater than on date Hierarchy in MDX

Question

I am new to MDX and I am trying to apply date filter to year of date Hierarchy , i.e. I want count of all sales for each product number excluding NA, for date greater than 1/1/2016.

Write now I am using below query :

 SELECT NON EMPTY { [Measures].[SALES COUNT] } ON COLUMNS
 , NON EMPTY { ([PRODUCT CRIETERIA].[PRODUCT NUMBER].[PRODUCT NUMBER].ALLMEMBERS ) } 
 DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
 FROM 
 ( 
 SELECT 
 ( -{ [PRODUCT CRIETERIA].[PRODUCT NUMBER].&[NA] } ) ON COLUMNS 
 FROM 
 ( 
 SELECT 
 ( 
 { 
 [PRODUCT Creation Date].[PRODUCT Creation Date Hierarchy].[PRODUCT Creation Year].&[2016], 
 [PRODUCT Creation Date].[PRODUCT Creation Date Hierarchy].[PRODUCT Creation Year].&[2017] 
 } 
 ) 
 ON COLUMNS FROM [Product_QA])) 
 WHERE ( [PRODUCT Creation Date].[PRODUCT Creation Date Hierarchy].CurrentMember ) 
 CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

But above query is working fine , but not right as I have to manually change this query each and every year. I want result where date > 1/1/2016 How can I use greater than here to get desired result.

Thanks.

Answer 1

I found the solution - in general when using the Date dimension a colon range operator with a NULL on either side gives a range that is open on one side e.g. 2016 and above

[PRODUCT Creation Date].[PRODUCT Creation Date Hierarchy].[PRODUCT Creation Year].&[2016] 
: NULL

upto and including 2016:

NULL:
[PRODUCT Creation Date].[PRODUCT Creation Date Hierarchy].[PRODUCT Creation Year].&[2016]