How to construct date range query when not all dates exist in hierarchy?

Question

New to MDX and inherited an application using the following to retrieve the last 7 work days.

Note, the actual dates are generated dynamically.

WHERE  ( [DimCalendar].[WorkDayHierarchy].[WorkDate].&[2016-03-25T00:00:00]
: [DimCalendar].[WorkDayHierarchy].[WorkDate].&[2016-03-26T00:00:00].lag(6)
)

I expected it to use the previous 7 work days:

2016-03-17 to 2016-03-25

But instead, it uses future dates

2016-03-25 to latest date in DimCalendar

From what I've read, it's because 2016-03-26 doesn't exist in the hierarchy, so the end range becomes NULL, which explains the future dates...

[WorkDate].&[2016-03-25T00:00:00] : NULL

The problem is the date values are generated dynamically, and I don't know in advance which values exist in the hierarchy. I'm not sure how to construct the MDX date range to get the desired results.

I've tried using <= and FILTER but keep getting conversion errors. With plain SQL this would be easy. I could just write:

WHERE [WorkDate] >= '2016-03-17' 
AND   [WorkDate] <= '2016-03-25'

Any ideas what the equivalent filter would be in MDX?

MoazRub · Accepted Answer · 2018-11-25T20:57:51.653

A quick fix could be

WHERE ( [DimCalendar].[WorkDayHierarchy].[WorkDate].&[2016-03-25T00:00:00].lag(7) : [DimCalendar].[WorkDayHierarchy].[WorkDate].&[2016-03-25T00:00:00] ) But this will only work if the past date is in the hierarchy, which in this case in 2016-03-25.

Edit: Based on the issue below

///Query without using strong names . (no &)

select {[Measures].[Internet Order Count] }
on columns,
[Date].[Day of Year].[1]:[Date].[Day of Year].[10]
on rows 
from [Adventure Works]

//This query filters by making the dimension member value, as a measure value.

WITH 
MEMBER [Measures].[Data Type] AS 
[Date].[Day of Year].CurrentMember.Properties ("Member_Value",TYPED) 
select {[Measures].[Internet Order Count] }
on columns,    
filter ([Date].[Day of Year].[Day of Year],[Measures].[Data Type]<12)
 on rows 
from [Adventure Works]

//You can also try the below one

select 
{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]} 
on columns, 
filter([Date].[Day of Year].[Day of Year], 
[Date].[Day of Year].currentmember.Properties ("Member_Value",TYPED)
>12 and [Date].[Day of Year].currentmember.Properties ("Member_Value",TYPED)<20) 
on rows 
from 
[Adventure Works]

Edit

//this might be the exact solution that would work for you

select 
{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]} 
on columns, 
([Geography].[Country].&[United States]
)
on rows 
from 
[Adventure Works]
where 
filter([Date].[Day of Year].[Day of Year], 
[Date].[Day of Year].currentmember.Properties ("Member_Value",TYPED)
>12 and [Date].[Day of Year].currentmember.Properties ("Member_Value",TYPED)<20)

Unfortunately it isn't known in advance whether the date is in the hierarchy - so it'll still the same problem :-/ Is there a way to get it to behave like a plain SQL range, like `.... where theDateCol >= 'startDate' and theDateCol <= 'endDate'`? — SOS, Nov 25 '18 at 03:25
the SQL range where value between 1 and 10 when translated to MDX will work with Measure values. For Hierarchy members the way to create range operator is using ":". This issue with your query is that you are using strong name "&[2016-03-25T00:00:00] the & operator indicates it". When there is no value for that strong name present in the hierarchy it translates to null .That was the orignal issue you were facing. there are two ways forward. 1) You list down the members of the hierarchy first, then make your above query. 2) Make a measure of the the dimension members name value and filter it. — MoazRub, Nov 25 '18 at 20:26
I have added some solutions to the orignal answer. They might help — MoazRub, Nov 26 '18 at 11:50

How to construct date range query when not all dates exist in hierarchy?

1 Answers1