topic Re: Expression in QlikView

Expression

Mon, 31 Mar 2014 07:07:47 GMT

Hi all,

I got this expression:

If(isnull((SUM({$< Year = {$(=(max(Year)))} >} RevenueAmount))/(SUM({$< Year = {$(=(max(Year)-1))} >} RevenueAmount))-1), 0, (SUM({$< Year = {$(=(max(Year)))} >} RevenueAmount))/(SUM({$< Year = {$(=(max(Year)-1))} >} RevenueAmount))-1)

However now I got my formula twice. Because the first part says if it's null you have to value it with 0. Then if it's not null it has to calculate the formula again. This will probably effect my system capacity so I thinking is there an other way to solve this?

kind regards

Re: Expression

jonathandienst — Mon, 31 Mar 2014 07:14:25 GMT

Use Alt():

=Alt(((SUM({$< Year = {$(=(max(Year)))} >} RevenueAmount)) / (SUM({$< Year = {$(=(max(Year)-1))} >} RevenueAmount))) - 1, 0)

Alt() will return the first parameter (your expression) if it is not null*, otherwise returns the second.

HTH

Jonathan

*actually if it is not numeric, but a null is non-numeric so it works in this case

Re: Expression

its_anandrjs — Mon, 31 Mar 2014 07:19:00 GMT

Another way you can use variables for this like

if(Isnull($(YourExpression),0 ,$(YourExpression))

Hope this helps

Thanks & Regards

Re: Expression

tresB — Mon, 31 Mar 2014 07:19:22 GMT

Try with alt(), like:

=Alt(yourExpression_once, 0)

Note: The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

Or, use you can use variable instead of direct expression. Using variable twice would not recalculate unlike you are afraid of performance.