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

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

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 05:19 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 04:17 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 04:20 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 04:57 PM

    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..

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 05:59 PM

    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
    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.