topic Re: FABS with variable? in QlikView

FABS with variable?

cbaqir — Fri, 11 Dec 2015 15:27:02 GMT

When I copied this expression into a text box, I see Error: Error in expression: ')' expected.

=IF(($(vForecastRound)=0 and $(vActualRound)=0), IsNull(Null()), IF(FABS($(vNewVariance))>1,0, 1-FABS($(vNewVariance))))

I think the issue is related to FABS($(vNewVariance)) because this alone gives me the same error. Does FABS() not work in this case for some reason?

Thanks,

Cassandra

Re: FABS with variable?

sunny_talwar — Fri, 11 Dec 2015 15:34:27 GMT

Does this alone works fine? -> $(vNewVariance)

Re: FABS with variable?

cbaqir — Fri, 11 Dec 2015 15:35:01 GMT

Yes

Re: FABS with variable?

sunny_talwar — Fri, 11 Dec 2015 15:37:04 GMT

What is the output of $(vNewVariance)? Can you share a screenshot?

Re: FABS with variable?

Mark_Little — Fri, 11 Dec 2015 15:38:44 GMT

Hi,

What do you see with what Sunny asked.

I just tested this simply with a variable with a minus in and then wrapped in FAB() and it works fine for me.

Mark

Re: FABS with variable?

cbaqir — Fri, 11 Dec 2015 15:39:53 GMT

Variance is $(vNewVariance). Forecast Accuracy is where I can't get the expression to work.

Re: FABS with variable?

Anonymous — Fri, 11 Dec 2015 15:41:57 GMT

Could you post a qvw? It can better help understand the situation.

Re: FABS with variable?

Mark_Little — Fri, 11 Dec 2015 15:42:00 GMT

Hi,

have you formatted the percentage on the table or is it actually returning 60.9%?

If the percentage sign is in the variable it maybe treated as a string.

Mark

Re: FABS with variable?

cbaqir — Fri, 11 Dec 2015 16:15:16 GMT

Sample QVW attached.

Re: FABS with variable?

cbaqir — Fri, 11 Dec 2015 17:32:59 GMT

The variable is not formatted with the percentage. It returns -0.6088... The formatting is done within the table.

Re: FABS with variable?

Anonymous — Fri, 11 Dec 2015 17:36:31 GMT

Put an equal sign in the beginning of the variable for vNewVariance

=IF($(vVariance)=0,0,IF($(vForecastRound)=0,IF($(vVariance)<0,-1,1), ($(vVariance)/ ($(vForecastRound))))).

Sounds silly but should work!

Re: FABS with variable?

Anonymous — Fri, 11 Dec 2015 17:40:12 GMT

Do this for all of your variables. From your sample it seems thats the issue. Let me know if it works.

Re: FABS with variable?

sinanozdemir — Fri, 11 Dec 2015 17:46:34 GMT

Hi,

I added "=" in vNewVariance variable. Can you check if it is producing the expected results.

Re: FABS with variable?

cbaqir — Fri, 11 Dec 2015 18:01:07 GMT

As pointed out to me by an earlier response, having the = causes issues such as:

https://community.qlik.com/message/931044?et=watches.email.thread#931044

Re: FABS with variable?

cbaqir — Fri, 11 Dec 2015 18:01:50 GMT

See above.

Re: FABS with variable?

sinanozdemir — Fri, 11 Dec 2015 20:24:47 GMT

I needed to test the below approach and it seems to be working so you need this FABS($(=$(=vNewVariance))). The calculated variable needs to be calculated as well.:

Hope this helps.

Thanks

Re: FABS with variable?

cbaqir — Fri, 11 Dec 2015 20:39:45 GMT

Sinan,

Thanks for the help but I am still a little confused. The original issue was that my Forecast Accuracy was not displaying. The expression in the chart is =$(vNewForecastAccuracy) and the variable expression is =IF(($(vForecastRound)=0 and $(vActualRound)=0),IsNull(Null()),IF(FABS($(vNewVariance))>1,0,1-FABS(($(vNewVariance))))) . Even if I update this to =IF(($(vForecastRound)=0 and $(vActualRound)=0),IsNull(Null()),IF(FABS($(=$(=vNewVariance)))>1,0,1-FABS($(=$(=vNewVariance))))) , it doesn't look right.

What am I missing?

The vNewVariance expression is defined as IF($(vVariance)=0,0,IF($(vForecastRound)=0,IF($(vVariance)<0,-1,1), ($(vVariance)/ ($(vForecastRound))))) .

Thanks,

Cassandra

Re: FABS with variable?

sinanozdemir — Fri, 11 Dec 2015 20:52:53 GMT

This is the expression in the forecast accuracy column:

Is the expression correct? It seems like the columns are not available any longer.

Re: FABS with variable?

cbaqir — Fri, 11 Dec 2015 21:07:14 GMT

No, I was playing with that expression to try to get it to work. It is normally populated with =$(vNewForecastAccuracy)

Re: FABS with variable?

rwunderlich — Fri, 11 Dec 2015 21:25:18 GMT

The general problem you are dealing with is the comments in your variables. The comments will get included in the expansion. So for example:

myvar = 1+1 // comment

=fabs($(myvar))

expands to:

=fabs(1+1) // comment)

Notice the right paren for fabs() is now after the comment -- a syntax error. Remove the comments from your variable defs and it will work.

How do I debug this? Remove the label from the Forecast Accuracy Expression. Now the column heading shows the expanded expression, and you can examine the full syntax.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com