How to ignore Zero and Blank using If else statement in Bex

[Former Member]

Hello Guru's,

Thanks in Advance,

i have a requirement as follows and i need to calculate Average from the same.

Year              BIAS

Jan 2016   

Feb 2016   

March 2016      0

April                 10

May                 0

June                25

July                

August             30

Sep                  40

Oct                  30

Nov                  25

Dec                  50

i have value for 9 Months including 0 and 3 months is Blank, my average need to be calculated as BIAS / 9(should be Dynamic), so i have created a Count(Formula variable) for BIAS where i have mentioned the Formula as (BIAS >= 0) * 1 + 0. ie if BIAS >= 0 then 1 else 0, with this count value i thought of taking the average calculation but the formula didn't work, it takes both 0 and Blank as same.

Please help me with a solution for the same. Hope i made my requirement clear.

Thank you,

Prabhu M.

[Loed]

Hi,

You may apply exception aggregation COUNTER FOR ALL VALUES THAT ARE NOT ZERO..

OR

You may suppress the months with ZERO values so you may achieve the correct count in your existing formula..

Regards,

Loed