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.
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
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.
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
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..
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.
Add a comment