Hi all,

I am trying to fill in the blanks with the last known
value. After reading Chp3 in George Spofford's book MDX
Solutions (which is a great book),
I found the following formula for creating a calculated
member on pg 68 that might do the trick:

"With Member [Measures].[Last Update of Price] AS

'iif(
Not IsEmpty ([Measures].[Price]),
[Measures].[Price],
iif([Time].PrevMember IS NULL, NULL,
([Measures].[Last Update of Price],
[Time].PrevMember)
)
)'"

However, when I try this code (which is a recursive
function), I am get the following error:

An unexpected Internal Error has occurred. Can anyone
please tell me what I am doing wrong and why I am getting
this error message?
Does MSAS not like recursive functions as calculated
members?

Better yet, can anyone please provide me with the MDX I
can use for filling in the blanks?


I am trying to use it in a Calculated Member called[List
Price].


This is what I currently have:

Day1 Day2 Day3 Day4 Day5

Prod1 Price $5 $5 $7




This is what I want:

Day1 Day2 Day3 Day4 Day5

Prod1 Price $5 $5 $5 $5 $7

Day3 and Day4 uses Day2's price of $5 even though Prod1
did not sell on Day3 and Day4.


I really do appreciate any assistance you can offer.

Best regards,

Jason
jmcguire_online at hotmail . com

This is a working Foodmart query that illustrates both the recursive and
non-recursive approaches to this problem.
The non-recursive version depends on your knowing the largest possible gap
and using the appropiate argument to the LastPeriods function.

with member Measures.LastNotEmpty as ' iif(isempty([Unit Sales]),
Tail(Filter (LastPeriods(100),Not IsEmpty([Unit Sales]))).Item(0) , [Unit
Sales]) '
member Measures.RecLastNotEmpty as ' iif(isempty([Unit
Sales]),(Time.Currentmember.PrevMember, RecLastNotEmpty), [Unit Sales]) '
select
Descendants( [1997],[Month]) on columns,
{ [Product].[Wine].[Pearl].Children * {[Unit Sales], LastNotEmpty,
RecLastNotEmpty} } on rows
from Sales
where ( [Customers].[CA])

Of course there's another approach: generate records in the fact table.

Each approach has pros and cons. It really depends on your specific scenario.
HTH,

Brian
www.geocities.com/brianaltmann/olap.html

"Please Help" wrote: