asked
MDX for records between date range where start and end date being separate columns
Hi Is it possible to write in MDX to extract records between two dates …. where start and end date are 2 diff columns in the same table Like if I write in SQL . Select Dimdate.Date, F.Col1 from Fact F join DimDate on (Dimdate.Date between F.D1start and F.D1end) // 1st date range condition where Dimdate.Date between F.D2start and F.D2end // 2nd date range condition and Dimdate.Date between F.D3start and F.D3end // 3rd date range condition and then in MDX SELECT [Measures].[Rowcount] on Columns, NonEmpty([Dimdate].[Date].members) on Rows From [CubeName] Where (Date range Joining conditions) // I know Where in MDX acts as slicer .. So like to know how I can put that and how . Finally I will have output Dates from Dimdate and corresponding measure values which are satisfying that date range
Yes, you can and it is quite easy.. you can write something like. SELECT { [Measures].[YourMeasure] } ON COLUMNS, NON EMPTY { [DimDate].[Date].&[FirstDateKey] : NULL } ON ROWS FROM [Cube] WHERE ( {NULL : [EndDateDimension].[Date].&[EndDateKey] ) Thea above assumes, tha the `[DimDate]` represents the starting date. If you have `[DimDate]` and additional two dimensions for Start and End date than the select will look like. SELECT { [Measures].[YourMeasure] } ON COLUMNS, NON EMPTY { [DimDate].[Date].MEMBERS } ON ROWS FROM [Cube] WHERE ( {[StartDateDimension].[Date].&[StartDateKey] : NULL} ,{NULL : [EndDateDimension].[Date].&[EndDateKey] )
Hi Pavel Thanks for the reply. I tried with the logic you suggested created role playing dimension .. but it seems I get all the rows from Dimdate rather than dates on required range. Here is the query for same i formed SELECT { [Measures].[Rowcount] } ON COLUMNS, NON EMPTY {[Date].[DATE].members} ON ROWS FROM [Cubename] WHERE ( {[D1Start].[DATE].&[2012-10-19T00:00:00]: NULL} ,{NULL : [D1End].[DATE].&[2012-11-03T00:00:00]},'extra condition ') Just to add further what I really want to achieve . I have below 8 ( 4 Start + 4 end) dates in the fact table . I need to create a measure which gives me dates satisfying the range for those 4 start and end dates 1 by 1. As you can see the intersect dates considering all are coming as 10/23/202 to 11/3/2012 ![alt text][1] The Start date can be any date.. so it has to be dynamic in the set If I create. So as I dropped this measure(dateRange) in the cube I get corresponding measure values i.e count,sum.. for that date like below ![alt text][2] [1]: /storage/temp/473-utm1.jpg [2]: /storage/temp/474-utm2.jpg I hope I am able to convey this .
Ah.. Ok.. then instead of only `NON EMPTY {[Date].[DATE].members} ON ROWS` try **`{Exists([Date].[DATE].members,, `"`Measure Group Name`"`)} ON ROWS`**. This will filter only dates which exists on your measure group and the WHERE part will take care about the range filtering.
Hello, I want to do similar this thing in Mondrial MDX, But it will give me error : Mondrian Error:Internal error: Cannot deduce type of call to function ':', Following is my Mondrial MDX query. WITH SET [~ROWS] AS Hierarchize({
{[Location_Cluster.default].[All Location_Cluster.defaults]}, {[Location_Cluster.default].[Location_Cluster].Members}}) member [Measures].[QTY Percent] as '[Measures].[Total_Quantity]/([Measures].[Total_Quantity], [Location_Cluster.default].[All Location_Cluster.defaults])', format_string='0.00%' member [Measures].[Revenue Percent] as '[Measures].[Total_Revenue]/([Measures].[Total_Revenue],[Location_Cluster.default].[All Location_Cluster.defaults])', format_string='0.00%' member [Measures].[Margin Percent] as '[Measures].[Total_Margin]/([Measures].[Total_Margin], [Location_Cluster.default].[All Location_Cluster.defaults])', format_string='0.00%' SELECT NON EMPTY {[Measures].[Sku_Count],[Measures].[Total_Quantity],[Measures].[QTY Percent],[Measures].[Total_Revenue],[Measures].[Revenue Percent],[Measures].[Total_Margin],[Measures].[Margin Percent]} ON COLUMNS, NON EMPTY [~ROWS] ON ROWS FROM [APCS_SALES_CUBE] Where ({[Date].[Start_Date].&[2017-01-01]: NULL} ,{NULL : [Date].[End_Date].&[2017-03-01]},[Cluster.Cluster_Id].[2],[Taxonomy.default].[Taxonomy_ID].[3],[Company.Company_Name].[Compnay_Name].[1]) Please provide me some help. ,
I'll just add a reference to a very good article which helped a lot in the exact same situation [MDX Between Start Date And End Date][1] I think you'll find this helpful [1]:
https://www.purplefrogsystems.com/blog/2013/04/mdx-between-start-date-and-end-date/ Regards Zvi
11 People are following this question.
