Previous non zero value

Question

Previous non zero value

anbu

SSC-Addicted

Points: 407
More actions
November 24, 2003 at 5:07 am

#106763

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Larry Stein Default port Points: 1447 More actions · Answer 1

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

hoosierpride Mr or Mrs. 500 Points: 565 More actions · Answer 2

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

steve.hughes@magenic.com

anbu SSC-Addicted Points: 407 More actions · Answer 3

Thanks for the replies and sorry for a delayed response from my side. But I have actually solved this problem with a database procedure.