How to hide a negative sum colum or set it to 0 ?

[Former Member]

Hello,

I'm trying to hide a negative sum column or if the sum is negative it shall be set to 0.

I defined a formula which shows the forecast for the year and in November I'm getting a negative (sum) forecast which is kind of confusing for the planner.

Formula:

( 'IST-Umsatz' + 'Plan-Umsatz' ) * 12 / ( 'Periode für Forecast' + 2 ) - ( 'IST-Umsatz' + 'Plan-Umsatz' )

In Period 10 my forecast is 0 and that's ok but in period 11 the formula does following 0 - 2.000.000 and the column has a negative value.

Is there a solution to my problem? In other words I want to hide or set it to 0 when I'm in period 11 and 12.

Thanks in advance.

[Former Member · Accepted solution]

Hello Alex,

Now it's working without an error but, the negative values are still displayed.

The code is more the confusing

( ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) * 12 / ( NODIM ( 'Periode für Forecast' ) + 2 ) - ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) ) >= 0 ) * ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) * 12 / ( NODIM ( 'Periode für Forecast' ) + 2 ) - ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) + ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) * 12 / ( NODIM ( 'Periode für Forecast' ) + 2 ) - ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) < 0 ) ) * 0

[Former Member · Accepted solution]

Hi,

Check the parenthesis. The last two parenthesis I think are wrong after <0 must have just one I think.


( ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) * 12 / ( NODIM ( 'Periode für Forecast' ) + 2 ) - ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) ) >= 0 ) * ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) * 12 / ( NODIM ( 'Periode für Forecast' ) + 2 ) - ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) + ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) * 12 / ( NODIM ( 'Periode für Forecast' ) + 2 ) - ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) < 0 """") )"""""" * 0

[Former Member]

Hi Alex,

I inserted another ( ) and now it's working.

( ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) * 12 / ( NODIM ( 'Periode für Forecast' ) + 2 ) - ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) ) >= 0 ) * ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) * 12 / ( NODIM ( 'Periode für Forecast' ) + 2 ) - ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) + ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) * 12 / ( NODIM ( 'Periode für Forecast' ) + 2 ) - ( ( NODIM ( 'IST-Umsatz' ) + NODIM ( 'Plan-Umsatz' ) ) < 0 ) ) * 0 )

Thanks Alec.

[Former Member]

Hello,

first thank you for your replies.

I'm struggling with the logical formula.

( ( ( 'IST-Umsatz' + 'Plan-Umsatz' ) * 12 / ( 'Periode für Forecast' + 2 ) - ( 'IST-Umsatz' + 'Plan-Umsatz' ) ) >= 0 ) *

( 'IST-Umsatz' + 'Plan-Umsatz' ) * 12 / ( 'Periode für Forecast' + 2 ) - ( 'IST-Umsatz' + 'Plan-Umsatz' ) +

THIS line give me an error when I execute the query - it tells me I shall insert NODIM somewhere ?

( ( 'IST-Umsatz' + 'Plan-Umsatz' ) * 12 / ( 'Periode für Forecast' + 2 ) - ( ( 'IST-Umsatz' + 'Plan-Umsatz' ) < 0 ) ) * 0

Without the last line my formula works, but the last line is the important part

I'm quite new to formulas, sorry..

[Former Member]

Hi,

The formula is not exact you have to add parenthesis. Try this.

( ( ( 'IST-Umsatz' + 'Plan-Umsatz' ) * 12 / ( 'Periode für Forecast' + 2 ) - ( 'IST-Umsatz' + 'Plan-Umsatz' ) ) >= 0 ) *

( 'IST-Umsatz' + 'Plan-Umsatz' ) * 12 / ( 'Periode für Forecast' + 2 ) - ( 'IST-Umsatz' + 'Plan-Umsatz' ) + ( ( nodim('IST-Umsatz') nodim( 'Plan-Umsatz') ) * 12 / ( nodim('Periode für Forecast' ) 2 ) - ( (nodim( 'IST-Umsatz') + nodim('Plan-Umsatz') ) < 0 ) ) * 0

Check nodim from the functions , don't write directly. If the formule have an error add nodim to all the operands. The error is because the bex detects you are calculating with operands with different units.

Regards

[Former Member]

Jason,

We use a rolling 13 week view, the last 7 weeks Act V's Bud and the next 6 week Bud only. I use the formula below to stop it working out the variance for the weeks in the future that will; only ever display Budget - as it's nonsensical to calculate a variance.

If Actual Sales and not equal to zero then work out the variance, otherwise present 0.

( 'Actual Sales' <> 0 ) * NDIV0 ( 'Actual Sales' % 'Budget Sales' ) + 0

Maybe something similar will work.

Regards

Gill

[Former Member]

Hi,

You can do a logic formula like.

(( 'IST-Umsatz' + 'Plan-Umsatz' ) * 12 / ( 'Periode für Forecast' + 2 ) - ( 'IST-Umsatz' + 'Plan-Umsatz' )) >=0)*

( 'IST-Umsatz' + 'Plan-Umsatz' ) * 12 / ( 'Periode für Forecast' + 2 ) - ( 'IST-Umsatz' + 'Plan-Umsatz' )+

( 'IST-Umsatz' + 'Plan-Umsatz' ) * 12 / ( 'Periode für Forecast' + 2 ) - ( 'IST-Umsatz' + 'Plan-Umsatz' )<0)*0

This formula checks the result of yuor formula. If the result is higher or equal than 0 the result is the formula but if the result is less than 0 the formula is multiplicated by 0.

I hope it helps.