Post reply

Previous non zero value

  • 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

  • December 8, 2003 at 9:13 am

    #485116

    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

    #486194

    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

  • January 7, 2004 at 5:07 am

    #488925

    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