Skip to Content
author's profile photo Former Member
Former Member

How to create a bEx formula to get the first value with conditions

Hello gurus,

I need to create a total column with some conditions to get the first month that have any value different of zero.

Example:

The column strutucture:

Jan (selection 0amount KF)

Feb (selection 0amount KF)

Mar (selection 0amount KF)

Apr (selection 0amount KF)

Total (formula)

In total formula, If value of Jan is different of zero than consideer Jan value only, but if we have zero in Jan then check if Feb value not zero, then if not zero consideer only Feb value etc.

I tried to do this, but in cases that we have values different of zero to Jan and Feb for example, we have Jan + Feb in the result but I only want Jan if Jan is the first column if value different zero:

(Jan value <> 0) * Jan + (Feb value <> 0) * Feb + (Mar <> 0) * Mar etc...

Thanks in advance,

Gustavo Sigrist Felippe

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Jul 29, 2012 at 08:32 AM

    Hi Gustavo

    I first started with the following which is working

    (Jan <> 0) * Jan +

    (Jan = 0 and Feb <> 0) * Feb +

    (Jan = 0 and Feb = 0 and Mar <> 0) * Mar

    but didn't like it so much because of the size, so i change it to the following

    (Jan <> 0) * Jan +

    (Jan = 0 and Feb <> 0) * Feb +

    (YTM1 = 0 and Mar <> 0) * Mar

    YTM1 is a subtotal of (Jan+Feb) and that goes on...

    You can decide which one is more easy for you to maintain in your query.

    Thank you

    Yiannis

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.