MDX Question on getting the top record for each....

I have scenario where a Item/Store combination can appear multiple times for the same period and to get the most recent record within a range ([Dim ZeroSalesDays].[Zero Sales Days]), I need to return the most current record. So I created measure with aggregation usage as last non empty.

So the below query works fine in that scenario.

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],(Aggregation usage – sum)

[Measures].[Last Tagged],[Measures].[Last Void](Aggregation usage – Last Non Empty)

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children},

{[Dim Store].[StoreName].Children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[30]}, {[Dim Period].[Period].[200702]}

)

Now they want to return [Dim ZeroSalesDays].[Zero Sales Days] value which was selected. How do I return that in the rows or columns. I can do order by and get the top record, but that will work only if I select a particular store and item, not for the entire set of all items/products.

with set

[Elapseddays] as

'order(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]},

cint([Dim ZeroSalesDays].[Zero Sales Days].value), desc)

'

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void]

} ON COLUMNS,

topcount(

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].&[4-DELMO -00090A]},

{[Dim Store].[StoreName].&[11733]},

{[Elapseddays]}

)

, 1)

ON ROWS

FROM sifdw

Any kind of help is appreciated.

Thank you in advance

Thank you,

Manish

Hi Manish,

Each row can include the last date of each individual Product/Store combination, like:

Code Snippet

SELECT

{[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void] } ON COLUMNS,

Generate(([Dim Product].[Item Id].Children, [Dim Store].[StoreName].Children),

Tail(NONEMPTY(([Dim Product].[Item Id].CurrentMember, [Dim Store].[StoreName].CurrentMember,

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]}),

([Dim Period].[Period].[200702], [Measures].[Tagged])))) ON ROWS

FROM sifdw

|||

Hi Deepak,

Somehow I was not able to get the query run successfully.

Let me attach the query what I have

SELECT

{

[Measures].[Last on Shelf]

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children}

,{[Dim Store].[Store Id].children}

,{[Dim Date].[Date].children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[25]},

{[Dim ZeroSales].[Zero Sales].&[61]},

{[Dim ZeroSales].[Zero Sales Period].&[402006]},

{[Dim ZeroSales].[Team ID].&[4]}

)

Now in this given scenario, for each item / store combination, there can be multiple date. I want the most recent date for Item/store combination (Rows should display 3 columns - Item, Store and Date).

Thank you,

Manish

|||

Hi Manish,

Could you explain the realtion between [Dim ZeroSalesDays] and [Dim Date] - the latter wasn't mentioned in your original description? And along which dimension is the most recent value (LastNonEmpty) found - since your original question related to determining the member along that dimension?

|||

Hi Deepak,

Sorry for the confusion.

[Dim ZeroSalesDays] and [Dim Date] are 2 dimension.

In the fact table, lets say we have these 6 colums. Item, Store, Date, ZerosalesDays and period are dimension and on shelf is a measure.

Now in a query, for a period '5', between 1 & 45 zero sales days, I wan't the most recent record for that Item/Store combination along with the date. (It should return me 2nd record, if I change the zero sales days between 1 and 25 then it should return me 1st record and so on)

Item Store Date On Shelf ZeroSalesDays Period

00016 0800209 11/02/2006 2 24 5
00016 0800209 11/15/2006 5 37 5

00016 0800209 11/25/2006 2 47 5

I hope I made it clear. Let me know if you need any other information.

I am trying to use Generate function... but it's just spinning for ever.. May be I am missing something.

Thank you,

Manish