question

Daniel-2027 avatar image
0 Votes"
Daniel-2027 asked Daniel-2027 commented

MDX Get minimum sales date for each item number

The query below pulls all dates with sales for each item number

I strictly want the minimum sales date for each item number, not all of them.

Example:
109985-image.png


I have tried using MIN in multiple ways but could not get it to work. Any help is appreciated.

 SELECT NON EMPTY 
 { [Measures].[Sales Units] } ON COLUMNS,
    
 NON EMPTY 
 { CROSSJOIN(([Item].[Item Number].[Item Number].ALLMEMBERS ),
 FILTER( [Date].[Date].[Date].AllMembers, [Date].[Date] >= [Item].[First Date Received]))} 
 DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
    
 FROM ( SELECT ( { [Item].[Item Number].&[USA]&[123456], [Item].[Item Number].&[USA]&[654321] } ) ON COLUMNS
 FROM [Database]) 
 CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


sql-server-analysis-services
image.png (5.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Was your problem solved ? Should the answer help? We would like to know your feedback! :)

1 Vote 1 ·

1 Answer

dgosbell avatar image
0 Votes"
dgosbell answered Daniel-2027 commented

If you have this as a date column in your sales table the easiest way to do this is to go into your cube design and create a measure with the MIN aggregation type over that date column. Then you could just use that measure along with the item number attribute in your query.

The other option would be a measure like the following, but the nonempty scan over the date members at run time will be more expensive than creating a measure.

WITH
MEMBER Measures.[First Sale] NONEMPTY( [Date].[Date].[Date].Members, Measures.[Sales Units] ).item(0).item(0).Name
SELECT
{ Measures.[First Sale] } on COLUMNS,
[Item].[Item Number].[Item Number].ALLMEMBERS ON ROWS
FROM ( SELECT ( { [Item].[Item Number].&[USA]&[123456], [Item].[Item Number].&[USA]&[654321] } ) ON COLUMNS
FROM [Database])

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Unfortunately I don't have access to the cube design but rather strictly end-user access.

We do have a [Date].[Date].[Date] but with the vast amount of data across the company, it times out well before finishing.

We have an hierarchy of [Date].[Yr-Mo-Wk-Day] with the members being [Year], [Month], [Week] and [Day].

Is there a way of using that filter it down first by [Year], then [Month], then [Week] and finally find the [Day] to try and improve the performance?


0 Votes 0 ·

Sorry for the delay responding as I had a family matter take me away from work, but your code worked perfectly and somehow allowed it complete unlike my prior attempts. Thank you!

I do have a follow-up question about have the date be >= a separate date field which is tricky because of formatting. I'll post is as a new question as I cannot seem to figure it out.

0 Votes 0 ·