Hi,

I have a cube with Date, Product and Activity dimension which will give
me what activity was carried out on which product and when. The measure
in this cube is quanity which will be minus if it is out and plus if it
is it type of activity.

My requirement: for a given date, I need to find the stock that do not
have activity for previous 30 days.

Any help on how to write MDX for this will be greatly appriciated.

Thanks
Mahesh



*** Sent via Developersdex http://www.developersdex.com ***

The following is psuedo code, you will have to fill in some of the
blanks to get it to work. It should return a list of products on the
rows for the date specified in the where clause that have not had any
activity in the last 30 days.

I am getting the entire set of product members and then for each member,
getting the set of days from the last 30 days that has a quantity
greater than 0. If the count of the members in this second set equals
zero then there has been no activity for that month.

SELECT

Filter(Product.[<level>].Members, Count(Filter(Time.Currentmember.Lag
(30):Time.CurrentMember, Measures.Quantity > 0)) = 0) ON ROWS,

{Measures.Quantity} ON COLUMNS

FROM [<Cube>]
WHERE ([Time].[Day].[18 Nov 2005])

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <ON07au86FHA.3588 (AT) TK2MSFTNGP15 (DOT) phx.gbl>, maheshnp (AT) hotmail (DOT) com
says...

Might want to try a NonEmptyCrossJoin() approach, since activities on a
given day may cause quantity to be <=> 0:

{Measures.Quantity} ON COLUMNS,
Except(Descendants([Product],,LEAVES),
NonEmptyCrossJoin(Descendants([Product],,LEAVES),
{[Time].Lag(30):[Time].PrevMember}, 1)) ON ROWS

FROM [<Cube>]
WHERE ([Time].[Day].[18 Nov 2005])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak, Maybe I misunderstood the original post.

I thought that the original poster was saying that if the measure was
positive it meant one thing and if it was negative it meant another (not
an ideal setup) and that they were only after products that did not have
a positive value, hence my use of the filter instead of the
nonemptycrossjoin approach.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <u4ChOp#6FHA.1032 (AT) TK2MSFTNGP11 (DOT) phx.gbl>,
deepak_puri (AT) progressive (DOT) com says...

Hi Darren,


For sure, parsing the semantics of questions posed here is not an exact
science; so maybe we'll have to wait to hear from Mahesh, on what
scenarios actually apply.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I agree with Darren's interpretation here. The problem sounds like classic
inventory scenario with things moving in and out - so NonEmptyCrossJoin
seems to be not appropriate here, but the Filter testing on non-zero values
is.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =

Hi Everybody,

Thank you very much for all the suggestions. I really appreciate such a
quick and detailed response.

I am trying out all the suggestions that have been made here and will
get back once i find out how they work.

Thank a lot.

Kind Regards
Mahesh




*** Sent via Developersdex http://www.developersdex.com ***

Hi,

As mentioned earlier by both Deepak and Darren, the quntity aggregation
of an itme for a given period can be 0 when total in quantity and total
out quantity for that period is equal. This will include that item in
the dead stock despite having multiple activities.

However filter method works perfectly if a new measure is created which
aggregates quantity on the basis of count instead of sum.

I am still trying out the NonEmptyCrossJoin method.

I really appreciate all the suggestions.

Thanks
Mahesh



*** Sent via Developersdex http://www.developersdex.com ***

Hi

I think that Deepak has right.
I have a folowing CM
iif(Product.currentmember.Level is Product.Product,

IsEmpty([Measures].[SalePositions])*[Measures].[StockValue],

Sum(Except(NonEmptyCrossJoin(Product.Product.membe rs, {[Measures].[Stock]},
1),

NonEmptyCrossJoin(Product.Product.members, {[Measures].[SalePositions]},
1)), [Measures].[StockValue]))

[Measures].[StockValue] is also CM

Vladimir Chtepa

"Mosha Pasumansky [MS]" <moshap (AT) online (DOT) microsoft.com> schrieb im Newsbeitrag
news:437e2100$1 (AT) news (DOT) microsoft.com...

Hi,

I have used both NonEmptyCrossJoin and Filter methods to solve my
problem. The NonEmptyCrossJoin is very slow as it is taking more than a
minute to process it. I am not sure if the time is consumed to find the
leave products by "Descendants(Products,,Leaves)" or by
NonEmptyCrossJoin.

The result i am getting from filter condition is not right. I think i
have some problem in my filter condition.

In order to dispaly the dead stock for 30 days i have to filter out the
products that do not have any activity for last 30 days. For this i have
created a calculated measure which counts the number of activities. I am
using the following expression.

SELECT
Filter( Descendants(Products,,Leaves),
(Time.CurrentMember.Lag(30):Time.CurrentMember,
Measures.ActivityCount)= 0) ON COLUMNS, ....

Here i have to filter the "Descendants(Products,,Leaves)" on the basis
of "Measures.ActivityCount" in
"Time.CurrentMember.Lag(30):Time.CurrentMember ". Can it be done with out
cross join.

To generalize this question how can i filter Dimension A (Products), on
the basis of its value for Measure A (Quantity) for a given preiod
(Time.. : Time.. )

Any help in this filter condition and optimimizing the NonEmptyCrossJoin
will be greatly appreciated.

Thanks and Regards
Mahesh







*** Sent via Developersdex http://www.developersdex.com ***