Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
cbaqirdhds
Valued Contributor

Not IsNull If Statement

I am trying to count the number of populated Resource fields per DEFECT_ID in order to use them as a grouping on a bar chart. Can someone please help me fix this? It is not working as intended. As you can see from the table below, the counts are not accurate. 4046 should show 4 but it shows 8.

='vBuildResourceCount: ' & sum(if(Not IsNull(CCL_RESOURCE),1,
if(Not IsNull(CHARGES_RESOURCE),1,
if(Not IsNull(CLIN_DOC_RESOURCE),1,
if(Not IsNull(CHARGES_RESOURCE),1,
if(Not IsNull(CORE_RESOURCE1),1,
if(Not IsNull(CORE_RESOURCE2),1,
if(Not IsNull(CPOE_RESOURCE),1,
if(Not IsNull(HIM_RESOURCE),1,
if(Not IsNull(LAB_RESOURCE),1,
if(Not IsNull(MPAGE_RESOURCE),1,
if(Not IsNull(MPAGE_CUSTOM_RESOURCE),1,
if(Not IsNull(PHARMACY_RESOURCE),1,
if(Not IsNull(PROVIDER_DOC_RESOURCE),1,
if(Not IsNull(RADIOLOGY_RESOURCE),1,
if(Not IsNull(RULES_RESOURCE),1,
if(Not IsNull(SURGERY_RESOURCE),1,
0)))))))))))))))))

1 Solution

Accepted Solutions

Re: Not IsNull If Statement

Hi Cassandra, maybe with this expression:

Sum(Aggr(RangeSum(

Not IsNull(CCL_RESOURCE),

Not IsNull(CHARGES_RESOURCE),

Not IsNull(CLIN_DOC_RESOURCE),

Not IsNull(CORE_RESOURCE1),

Not IsNull(CORE_RESOURCE2),

Not IsNull(CPOE_RESOURCE),

Not IsNull(HIM_RESOURCE),

Not IsNull(LAB_RESOURCE),

Not IsNull(MPAGE_RESOURCE),

Not IsNull(MPAGE_CUSTOM_RESOURCE),

Not IsNull(PHARMACY_RESOURCE),

Not IsNull(PROVIDER_DOC_RESOURCE),

Not IsNull(RADIOLOGY_RESOURCE),

Not IsNull(RULES_RESOURCE),

Not IsNull(SURGERY_RESOURCE)

),DEFECT_ID))*-1

3 Replies

Re: Not IsNull If Statement

Hi Cassandra, maybe with this expression:

Sum(Aggr(RangeSum(

Not IsNull(CCL_RESOURCE),

Not IsNull(CHARGES_RESOURCE),

Not IsNull(CLIN_DOC_RESOURCE),

Not IsNull(CORE_RESOURCE1),

Not IsNull(CORE_RESOURCE2),

Not IsNull(CPOE_RESOURCE),

Not IsNull(HIM_RESOURCE),

Not IsNull(LAB_RESOURCE),

Not IsNull(MPAGE_RESOURCE),

Not IsNull(MPAGE_CUSTOM_RESOURCE),

Not IsNull(PHARMACY_RESOURCE),

Not IsNull(PROVIDER_DOC_RESOURCE),

Not IsNull(RADIOLOGY_RESOURCE),

Not IsNull(RULES_RESOURCE),

Not IsNull(SURGERY_RESOURCE)

),DEFECT_ID))*-1

MVP
MVP

Re: Not IsNull If Statement

Use this

='vBuildResourceCount: ' &

RangeSum(COUNT(Distinct if(Not IsNull(CCL_RESOURCE),CCL_RESOURCE))

,COUNT(Distinct if(Not IsNull(CHARGES_RESOURCE),CHARGES_RESOURCE))

,COUNT(Distinct if(Not IsNull(CLIN_DOC_RESOURCE),CLIN_DOC_RESOURCE))

+COUNT(Distinct if(Not IsNull(CHARGES_RESOURCE),CHARGES_RESOURCE))

+COUNT(Distinct if(Not IsNull(CORE_RESOURCE1),CORE_RESOURCE1))

+COUNT(Distinct if(Not IsNull(CORE_RESOURCE2),CORE_RESOURCE2))

+COUNT(Distinct if(Not IsNull(CPOE_RESOURCE),CPOE_RESOURCE))

+COUNT(Distinct if(Not IsNull(HIM_RESOURCE),HIM_RESOURCE))

+COUNT(Distinct if(Not IsNull(LAB_RESOURCE),LAB_RESOURCE))

+COUNT(Distinct if(Not IsNull(MPAGE_RESOURCE),MPAGE_RESOURCE))

+COUNT(Distinct if(Not IsNull(MPAGE_CUSTOM_RESOURCE),MPAGE_CUSTOM_RESOURCE))

+COUNT(Distinct if(Not IsNull(PHARMACY_RESOURCE),PHARMACY_RESOURCE))

+COUNT(Distinct if(Not IsNull(PROVIDER_DOC_RESOURCE),PROVIDER_DOC_RESOURCE))

+COUNT(Distinct if(Not IsNull(RADIOLOGY_RESOURCE),RADIOLOGY_RESOURCE))

+COUNT(Distinct if(Not IsNull(RULES_RESOURCE),RULES_RESOURCE))

+COUNT(Distinct if(Not IsNull(SURGERY_RESOURCE),SURGERY_RESOURCE)))

cbaqirdhds
Valued Contributor

Re: Not IsNull If Statement

Thanks!