Missing Values in a formula

[Anonymous]

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.

[jagan]

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.

[Anonymous]

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.

[jagan]

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.