November 24, 2003 at 5:07 am
Hello,
I have a situation like this.
I have the daily ending balance of each item and for some days there are no transactions for some of the items. Those items have zero ending balance but what I need is to get the previous non zero value.
Do any one of have any idea how to get it. Please reply ASAP. It is really very urgent.
Thanks in advance,
Anbu
December 2, 2003 at 12:00 pm
This was removed by the editor as SPAM
December 8, 2003 at 9:13 am
I don't know MDX well enough (at all!) to know a solution there. But there are often other ways to filet the feline.
Is your data set small enough that you can filter the data in SQL first and do a full process on the cube? Perhaps the data set of interest can be formulated in a SQL view and the cube simply point to that view. The downside is needing to fully process every day. A simple example -
create view latest_non_0_balance as
select acct#, balance
from daily_acct_balance b1
where balance_date = (
select max(balance_date)
from daily_acct_balance b2
where b2.acct# = b1.acct#
and b2.balance <> 0)
Is this what you are looking for?
Good luck,
Larry
Larry
December 15, 2003 at 9:45 am
If your daily balance is a calculated member based on a specific time member, you could test the value and if it equals 0 return the previous member which will recurse until it reaches a non-zero member.
Sample:
Calculated Members:
DailyBalanceActual (what you are doing now)
DailyBalanceAdjusted (what you want to display)
iif(DailyBalanceActual = 0, (Time.PrevMember, DailyBalanceActual), DailyBalanceActual)
The syntax may not perfectly match what you are doing, but it will keep trying to find a non-zero value. You may need to determine how many times you want go back. To do this put conditional logic in the "then" portion of the statement.
Hope that helps.
Steve Hughes
Magenic Technologies
January 7, 2004 at 5:07 am
Thanks for the replies and sorry for a delayed response from my side. But I have actually solved this problem with a database procedure.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply