If Statements

[cbaqir]

I need to write basically an elseif statement in an expression...

This works:

if(isnull($(vForecastVariance)), -1, $(vForecastVariance))

This doesn't:

=if(isnull(vForecastRound) and isnull(vActualRound),0, if(isnull($(vForecastVariance)), -1, $(vForecastVariance)))

I think it's a syntax error. Any suggestions?

Thanks!

Cassandra

[sunny_talwar]

I don't see any issue with the syntax. What is the error that you are seeing? May be its something to do with one of the variables you have in there.

[cbaqir]

If Forecast and Actual are 0, Variance % should be 0%.

If Forecast is 0, Variance % should be -100%.

[rwunderlich]

When you reference vForecastVariance, you use the $() to evaluate it. For the other two variables you do not use $(). Are they constants?

Also not that 0 and null ar different. That ifNull(0) = false.

-Rob

[maxgro]

if(sum(forecast)=0 and sum(actual)=0,

  0,

  sum(forecast) / sum(actual) -1

  )

[cbaqir]

I updated all variables to use $() but it did not change the end result.

If vForecastRound and vActualRound are 0, I want the expression to show 0 (which would show 0%).

[cbaqir]

There are 2 if statements to merge:

if ($(vForecastRound)=0 and ($(vActualRound))=0,-1, $(vForecastVariance))

or

if($(vForecastVariance))=0, 0, $(vForecastVariance)))

[tamilarasu]

Hi Cassandra,

Could you split the statement and do a test whether the results are fine or not.

=if(($(vForecastRound)=0 and $(vActualRound)=0),0,1)

=if($(vForecastVariance)=0, -1, $(vForecastVariance))

Also add expressions for the variables =$(vForecastRound), =$(vActualRound) and =$(vForecastVariance). So that you can see where you have the problem.

If both works correctly, then the below should work.

=if(($(vForecastRound)=0 and $(vActualRound)=0),0,if($(vForecastVariance)=0, -1, $(vForecastVariance)))

[cbaqir]

If Forecast is 0, Variance % should be -100%.

In the case of the first line, I would expect Variance to be -100%.

=if(($(vForecastRound)=0 and $(vActualRound)=0),0,1)  = 1

=if($(vForecastVariance)=0, -1, $(vForecastVariance)) = 0

[#10 · Accepted solution]

You may need something like this:

if ($(vForecastRound)=0 and ($(vActualRound))=0,0, if($(vForecastRound)=0, -1, $(vForecastVariance)))

Please see my test. keep loading and you can test different data set.

Thanks.