topic Re: If Statements in QlikView

If Statements

cbaqir — Fri, 20 Nov 2015 18:54:54 GMT

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

Re: If Statements

sunny_talwar — Fri, 20 Nov 2015 18:59:18 GMT

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.

Re: If Statements

cbaqir — Fri, 20 Nov 2015 19:01:52 GMT

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

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

Re: If Statements

rwunderlich — Fri, 20 Nov 2015 19:13:57 GMT

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

Re: If Statements

maxgro — Fri, 20 Nov 2015 19:20:07 GMT

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

sum(forecast) / sum(actual) -1

)

Re: If Statements

cbaqir — Fri, 20 Nov 2015 19:57:58 GMT

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

Re: If Statements

cbaqir — Fri, 20 Nov 2015 20:44:22 GMT

There are 2 if statements to merge:

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

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

Re: If Statements

tamilarasu — Sat, 21 Nov 2015 04:37:23 GMT

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

Re: If Statements

cbaqir — Mon, 23 Nov 2015 20:00:19 GMT

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

Re: If Statements

Tue, 24 Nov 2015 02:33:07 GMT

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.

Re: If Statements

cbaqir — Tue, 24 Nov 2015 14:32:31 GMT

Thank you!