topic Re: Missing Values in a formula in QlikView

Missing Values in a formula

Anonymous — Thu, 05 Feb 2015 03:52:49 GMT

hi,

I have complex formula... as below, and this formula is being used by other calculations as well..

My problem is, if any component of the formula is missing or has missing value,, it affected the computation and thus has cascading effect on the rest of the dependent calculations..

round(((sum(distinct {<CCM_MAJOR_TYPE= {'ALL'}>} CCM_TOTAL_ENROLMENT)*column(2))

-count(DISTINCT {<CCM_MAJOR_TYPE= {'ALL'},CCM_PLAN_TYPE={'1MJ*'}>} CCM_STUDENTID))

+count(DISTINCT {<CCM_MAJOR_TYPE= {'ALL'},CCM_PLAN_TYPE={'1MJ*'},CCM_TAKEN_OVERALL={'N*'}>} CCM_STUDENTID),0.001),sum(distinct {<CCM_MAJOR_TYPE= {'ALL'}, CCM_PLAN_TYPE={'1MJ*'} >} CCM_TOTAL_ENROLMENT)))

If any of the item has missing value i already got null for the rest of the calculations..

example for this segment

count(DISTINCT {<CCM_MAJOR_TYPE= {'ALL'},CCM_PLAN_TYPE={'1MJ*'}>} CCM_STUDENTID)

how do i trap if this expression would return a missing value and enforced zero instead for the calculations would still go.,,

really appreciate any help..

thanks.

Re: Missing Values in a formula

jagan — Thu, 05 Feb 2015 04:04:02 GMT

Hi,

You can try RangeSum() or Alt()

round((

(Alt(sum(distinct {<CCM_MAJOR_TYPE= {'ALL'}>} CCM_TOTAL_ENROLMENT), 0)*Alt(column(2), 0))

-Alt(count(DISTINCT {<CCM_MAJOR_TYPE= {'ALL'},CCM_PLAN_TYPE={'1MJ*'}>} CCM_STUDENTID), 0))

+Alt(count(DISTINCT {<CCM_MAJOR_TYPE= {'ALL'},CCM_PLAN_TYPE={'1MJ*'},CCM_TAKEN_OVERALL={'N*'}>} CCM_STUDENTID), 0),0.001),

Alt(sum(distinct {<CCM_MAJOR_TYPE= {'ALL'}, CCM_PLAN_TYPE={'1MJ*'} >} CCM_TOTAL_ENROLMENT),0)))

Hope this helps you

Regards,

Jagan.

Re: Missing Values in a formula

Anonymous — Thu, 05 Feb 2015 05:47:00 GMT

hi jagan,

thx for the fast reply,, have tried this.. unfortunatey it still showing blank for missing items..

i used this formula in a pivot table..

my row are courses and my column is year...

if a course is not taken for a specfic year,, then thats where the missing value applies and affects the rest of the calculations.

thx.

Re: Missing Values in a formula

jagan — Thu, 05 Feb 2015 05:57:19 GMT

Hi,

Check each expression separately outside the chart in text object and make sure that alt is working correctly, like below

Alt(sum(distinct {<CCM_MAJOR_TYPE= {'ALL'}>} CCM_TOTAL_ENROLMENT), 0)

If above Sum() returns Null then you will get 0.

Regards,

Jagan.