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

[Former Member]

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

[former_member223480 · Accepted solution]

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

[Former Member · Accepted solution]

Hi,

In Ist Col  (Jan <>0)* Jan   ( Put This condition only)

In IInd col  ( Jan == 0 ann Feb <> 0 ) * Feb  ( Put this condition Only )

In IIIrd col  ( Jan == 0 and Feb == 0 and Mar <>0 ) * Mar ( this condition only )

If you want it should display value , only if all the values are zero except that month value non zero , then conditions will be different.

Regards,

Anand Mehrotra.

[Former Member · Accepted solution]

Thanks guys,

These 2 ways it works for me!