topic Division by NULL in QlikView

Division by NULL

Tue, 03 Dec 2013 15:23:43 GMT

Am trying to work out a formula which calculates the increase/decrease in sales (premium) for a selected year compared to the previous year.

Formula below works fine except in one circumstance where the sales (premium) for the previous year (year-1) is empty.

if((sum({$<Year = {$(=Only(Year))}>} Premium) -
(sum({$<Year = {$(=Only(Year)-1)}>} Premium)))/
(sum({$<Year = {$(=Only(Year)-1)}>} Premium))>=0,1,0)))

Any suggestions please?

thanks!

steve

Re: Division by NULL

Clever_Anjos — Tue, 03 Dec 2013 15:26:12 GMT

alt(

if((sum({$<Year = {$(=Only(Year))}>} Premium) -
(sum({$<Year = {$(=Only(Year)-1)}>} Premium)))/
(sum({$<Year = {$(=Only(Year)-1)}>} Premium))>=0,1,0))),

0 // or another default value

)

Re: Division by NULL

Tue, 03 Dec 2013 15:33:33 GMT

formula doesn't accept anything after the >=0,1))

Re: Division by NULL

Clever_Anjos — Tue, 03 Dec 2013 15:34:53 GMT

Sorry, too much ")"

=alt(

if((sum({$<Year = {$(=Only(Year))}>} Premium) -

(sum({$<Year = {$(=Only(Year)-1)}>} Premium)))/

(sum({$<Year = {$(=Only(Year)-1)}>} Premium))>=0,1,0),

0 // or another default value

)

Re: Division by NULL

angelaecheverri — Tue, 03 Dec 2013 18:58:11 GMT

i guess in order to have a better performance...

use in chart propieties, presentation tab unselect null symbol and missing symbols at the left end of the windown

and use a simple expresion

(

sum({$<Year = {$(=Only(Year))}>} Premium) -
sum({$<Year = {$(=Only(Year)-1)}>} Premium)

) /
sum({$<Year = {$(=Only(Year)-1)}>} Premium)