MDX Filter / Date Range Question

Question

MDX Filter / Date Range Question

rob26r

SSC Veteran

Points: 282
More actions
August 9, 2018 at 3:45 pm

#369002
Trying to accomplish the following:
- How many customers purchased from a specific product for any given date range
- What were the total sales for customers noted above for any given date range
Current query:
- My query below gives me exactly what I want:
WITH SET Customer_CurrentPeriod as
FILTER (
[CUSTOMER MASTER].[Cust#].children ,
(
[Calendar].[Date].&[20180507]
, [Item Master].[Sub Group Number].&[100]
, [Measures].[Ext Price]
) > 0
)
member Sales_CurrentPeriod as
sum (
([Customer_CurrentPeriod] , [Calendar].[Date].&[20180507])
, [Measures].[Ext Price]
)
member Sales_Sum_CurentPeriod_Count as [Customer_CurrentPeriod].Count
SELECT
{Sales_CurrentPeriod , Sales_Sum_CurentPeriod_Count} ON COLUMNS
from [SalesDatabase]

PROBLEM
- When I try to change the date to a date range (code belas shown below I get the error message. Query (4, 6) The GreaterThan function expects a string or numeric expression for the 1 argument. A tuple set expression was used.
WITH SET Customer_CurrentPeriod as
FILTER (
[CUSTOMER MASTER].[Cust#].children ,
(
{[Calendar].[Date].&[20180507]:[Calendar].[Date].&[20180507]}
, [Item Master].[Sub Group Number].&[100]
, [Measures].[Ext Price]
) > 0
)
member Sales_CurrentPeriod as
sum (
([Customer_CurrentPeriod] , {[Calendar].[Date].&[20180507]:[Calendar].[Date].&[20180507]})
, [Measures].[Ext Price]
)
member Sales_Sum_CurentPeriod_Count as [Customer_CurrentPeriod].Count
SELECT
{Sales_CurrentPeriod , Sales_Sum_CurentPeriod_Count} ON COLUMNS
from [SalesDatabase]

I'm obviously new to MDX so can anyone help figure out what I'm doing wrong? Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

rob26r SSC Veteran Points: 282 More actions · Answer 1

For anyone else who has a similar issue - you need to aggregate the field. https://stackoverflow.com/questions/37047150/how-to-query-date-field-in-mdx-with-and-without-aggregate-group