|
Home > Archive > MS SQL Server OLAP > August 2005 > Inventory problem revisited
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Inventory problem revisited
|
|
| Sjoerd 2005-08-29, 7:23 am |
| 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=AD, 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
| |
| Brian Altmann 2005-08-29, 11:23 am |
| 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:
> 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
>
>
| |
| Mosha Pasumansky [MS] 2005-08-29, 1:24 pm |
| 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(TIM
E.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@yahoo
.com> wrote in message
news:1125310592.581889.259370@g49g2000cwa.googlegroups.com...
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
|
|
|
|
|