help with expression not excluding nulls

rcorcoran — Tue, 20 Nov 2018 11:59:18 GMT

I have an expression that I was using to bring in target data for a table and it worked fine:

if(ISNULL(

NUM(AVG( {$<TargetCode={'CPA27'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={6}, ReferredMM=, YearMonth=>} [AnnualTarget]), format.percentage.decimal1)

), NULL(),

NUM(AVG( {$<TargetCode={'CPA27'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={6}, ReferredMM=, YearMonth=>} [AnnualTarget]), format.percentage.decimal1))

However I need to change it so that I can hard code targets for specific Service Provider numbers and have the code in below which is bringing in the correct data but also showing up hospitals that are blank that were excluded with the first code

IF(ISNULL(

if(ServiceProviderNum='5099','≤5.0',
if(ServiceProviderNum='5006','≤6.2',
if(ServiceProviderNum='5004','≤4.2',
if(ServiceProviderNum='5007','≤5.4',
if(ServiceProviderNum='5005','≤5.3',
if(ServiceProviderNum='5008','≤4.6',
if(ServiceProviderNum='5002','≤4.1',

NUM(AVG( {$<TargetCode={'CPA27'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={6}, ReferredMM=, YearMonth=>} [AnnualTarget]), format.percentage.decimal1))))))))), NULL(),

'≤' & NUM(AVG( {$<TargetCode={'CPA27'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={6}, ReferredMM=, YearMonth=>} [AnnualTarget]), format.percentage.decimal1)))))))))

any help on how to exclude the service Provider numbers that are blank would be great. I have exclude nulls in the dimensions already

Thanks

Re: help with expression not excluding nulls

sunny_talwar — Tue, 20 Nov 2018 12:11:12 GMT

Would you be able to share an image of what you are seeing? A sample might be helpful as well

topic help with expression not excluding nulls in QlikView

help with expression not excluding nulls

Re: help with expression not excluding nulls