Not IsNull If Statement

[cbaqir]

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)))))))))))))))))

[rubenmarin · Accepted solution]

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

[MK_QSL]

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)))

[cbaqir]

Thanks!