Hi all,

An often heard solution to the inventory problem (where you want to
know the last status in contrary to summing over periods) is the next
formula

(MEASURES.[NR OF ITEMS], TAIL(
FILTER(
DESCENDANTS(TIME.CURRENTMEMBER*, TIME.MONTH)
, NOT ISEMPTY(MEASURES.[NR OF ITEMS]))
,1).ITEM(0))

This formula looks at the last month for which there are nr of items
available. For example, lets say the following figures (nr of items)
are available

Nov 2004 Dec 2004
Pens 5 3
Pencils 8 9

The formula above will then return 3 Pens and 9 Pencils for 2004. This
is exactly what we want. However, lets say you also have markers, but
did not have any available in december 2004:

Nov 2004 Dec 2004
Pens 5 3
Pencils 8 9
Markers 10 NULL

The formula now returns 3 Pens, 9 Pencils and 10! Markers for 2004.

What we want to achieve is that the formula returns NULL Markers for
2004. This because there are items available in december, but no
Markers. How can we accomplish this in a generic way?

With kind regards,
Sjoerd

If I understand you correctly, see the thread titled "Variance to the Stock
Problem", dated 8/26/2005.

HTH,
--
Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html


"Sjoerd" wrote:

If I understand you correctly - you want LastChild semiadditive aggregation
instead of LastNonEmptyChild. In AS2005 - it is just a matter of choosing
right measure aggregation function.
In AS2000, if you need to get to last child, then simply drop Filter over
Not IsEmpty from the formula - i.e.

(MEASURES.[NR OF ITEMS], TAIL(DESCENDANTS(TIME.CURRENTMEMBER*,
TIME.MONTH),1).ITEM(0))

--
==============================*=================== =
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.
==============================*=================== =
"Sjoerd" <sjoerd_janssen (AT) yahoo (DOT) com> wrote

Hi all,

An often heard solution to the inventory problem (where you want to
know the last status in contrary to summing over periods) is the next
formula

(MEASURES.[NR OF ITEMS], TAIL(
FILTER(
DESCENDANTS(TIME.CURRENTMEMBER*, TIME.MONTH)
, NOT ISEMPTY(MEASURES.[NR OF ITEMS]))
,1).ITEM(0))

This formula looks at the last month for which there are nr of items
available. For example, lets say the following figures (nr of items)
are available

Nov 2004 Dec 2004
Pens 5 3
Pencils 8 9

The formula above will then return 3 Pens and 9 Pencils for 2004. This
is exactly what we want. However, lets say you also have markers, but
did not have any available in december 2004:

Nov 2004 Dec 2004
Pens 5 3
Pencils 8 9
Markers 10 NULL

The formula now returns 3 Pens, 9 Pencils and 10! Markers for 2004.

What we want to achieve is that the formula returns NULL Markers for
2004. This because there are items available in december, but no
Markers. How can we accomplish this in a generic way?

With kind regards,
Sjoerd