Semi Additive Measures : Inventory

#1

This example is from George Spofford's book MDX Solutions
pg 68. I think it will get you on track.

WITH MEMBER [Measures].[Last Qty On Hand] AS
'iif ( NOT IsEmpty ( [Measures].[Qty On Hand]),
[Measures].[Qty On Hand],

iif ( [Time].PrevMember IS NULL,
NULL,
([Measures].[Last Qty On Hand], [Time].PrevMember)
)
)'

Quote:

-----Original Message-----

Data for 1st Quarter, 2nd Quarter is present but for the
3rd Quarter
there is no record for the current month(August) but for
there are
records for July.

I have used the following :

CoalesceEmpty(([Measures].[Qty On Hand], ClosingPeriod(
[AP
Time].[Week]) )

,([AP Time].CurrentMember.LastChild, [Measures].[Qty On
Hand]))

and even tried:

Iif(

IsEmpty(([Measures].[Qty On Hand],ClosingPeriod( [AP
Time].[Week]) )),

([AP Time].CurrentMember.LastChild, [Measures].[Qty On
Hand]),

([Measures].[Qty On Hand],ClosingPeriod( [AP Time].
[Week]) )

)

I am trying to get the value for July using :

([AP Time].CurrentMember.LastChild, [Measures].[Qty On
Hand])

but since LastChild is August and data is not found it
returns NULL.

I was wondering if there is any MDX function that could
get me the last
value for July only.

--
Posted via http://dbforums.com
.

#2

Thanks alot, however, I forgot to mention that it is a type of recursive
queries where if July too is Null it should pick up the value of June or
May or April etc which ever is the last value available.

Would appreciate some suggestions.

Originally posted by Mwl

Quote:

This example is from George Spofford's book MDX Solutions

pg 68. I think it will get you on track.

WITH MEMBER [Measures].[Last Qty On Hand] AS

'iif ( NOT IsEmpty ( [Measures].[Qty On Hand]),

[Measures].[Qty On Hand],

iif ( [Time].PrevMember IS NULL,

NULL,

([Measures].[Last Qty On Hand], [Time].PrevMember)

)

)'

-----Original Message-----

Data for 1st Quarter, 2nd Quarter is present but for the

3rd Quarter

there is no record for the current month(August) but for

there are

records for July.

I have used the following :

CoalesceEmpty(([Measures].[Qty On Hand], ClosingPeriod(

[AP

Time].[Week]) )

,([AP Time].CurrentMember.LastChild, [Measures].[Qty On

Hand]))

and even tried:

Iif(

IsEmpty(([Measures].[Qty On Hand],ClosingPeriod( [AP

Time].[Week]) )),

([AP Time].CurrentMember.LastChild, [Measures].[Qty On

Hand]),

([Measures].[Qty On Hand],ClosingPeriod( [AP Time].

[Week]) )

)

I am trying to get the value for July using :

([AP Time].CurrentMember.LastChild, [Measures].[Qty On

Hand])

but since LastChild is August and data is not found it

returns NULL.

I was wondering if there is any MDX function that could

get me the last

value for July only.

--

Posted via
http://dbforums.com/http://dbforums.com

.

--
Posted via http://dbforums.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Semi Additive Measures : Inventory

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Semi Additive Measures : Inventory - 08-20-2003 , 09:11 AM

Re: Semi Additive Measures : Inventory - 08-21-2003 , 08:15 AM