topic Null to Zero in Set Analysis in QlikView

Null to Zero in Set Analysis

Fri, 07 Jan 2011 11:02:25 GMT

Hi,

The below expression will return null for some of the rows. I need it to be 0, instead of null. Is there any efficient way of achieving this scenario, instead of using ISNULL function.

=IF(vMas=$(vMat),
SUM(AGGR(ONLY({<A_FLAG={'1'}>+<B_FLAG={'1'}>+<C_FLAG={'1'}>}TRADE), SK, PER)) /
SUM(AGGR(ONLY({<A_FLAG={'1'}>+<B_FLAG={'1'}>+<C_FLAG={'1'}>}VOLUME), SK, PER)))

Thanks

Null to Zero in Set Analysis

johnw — Sat, 08 Jan 2011 02:44:36 GMT

You could probably wrap the whole thing in a rangesum() function. The rangesum() function treats any null parameters like they are zero. With one parameter, if it is null, I'd expect it to return 0 like you want.

Null to Zero in Set Analysis

maneshkhottcpl — Sat, 08 Jan 2011 09:58:10 GMT

Hi,

Please try this if u dont find any way to implement, it will sure work.

= if(

(IF(vMas=$(vMat),
SUM(AGGR(ONLY({<A_FLAG={'1'}>+<B_FLAG={'1'}>+<C_FLAG={'1'}>}TRADE), SK, PER)) /
SUM(AGGR(ONLY({<A_FLAG={'1'}>+<B_FLAG={'1'}>+<C_FLAG={'1'}>}VOLUME), SK, PER)))

)<1,'0',IF(vMas=$(vMat),
SUM(AGGR(ONLY({<A_FLAG={'1'}>+<B_FLAG={'1'}>+<C_FLAG={'1'}>}TRADE), SK, PER)) /
SUM(AGGR(ONLY({<A_FLAG={'1'}>+<B_FLAG={'1'}>+<C_FLAG={'1'}>}VOLUME), SK, PER)))

)