0 Replies Latest reply: Aug 29, 2016 11:29 AM by Rhona Corcoran

# if is null addition not working

Hi I have an expression in a pivot table as follows.  My problem with it is for the first expression where it is adding two figures (PallCare Paeds 1 =1 and PallCare Paeds 2 = 1 it is not showing up if one of them has no value.  It will show up if they both have values in.  I need it to show the value if only one has a value or if both do.

Any help appreciated

if(isnull(Pick(Match(ValueList(\$(vPallCareChildren)),\$(vPallCareChildren)),

num((Sum({\$<[PallCare Paeds1]={1},ReferredMM={1}>}ActualValue)) + (Sum({\$<[PallCare Paeds2]={1},ReferredMM={1}>}ActualValue)), '#,##0'),

num(Avg({\$<[PallCare Paeds2]={1},ReferredMM={1}>}ActualValue), '#,##0'),

num(Avg({\$<[PallCare Paeds1]={1},ReferredMM={1}>}ActualValue), '#,##0'),

num((Avg({\$<[PallCare Paeds1]={5},ReferredMM={1}>}ActualValue)) + (Avg({\$<[PallCare Paeds2]={5},ReferredMM={1}>}ActualValue)) +(Avg({\$<[PallCare Paeds2]={72},ReferredMM={1}>}ActualValue)), '#,##0'),

num(Avg({\$<[PallCare Paeds2]={5,72},ReferredMM={1}>}ActualValue), '#,##0'),

num(Avg({\$<[PallCare Paeds1]={5},ReferredMM={1}>}ActualValue), '#,##0'))),Null(),

Pick(Match(ValueList(\$(vPallCareChildren)),\$(vPallCareChildren)),

num((Sum({\$<[PallCare Paeds1]={1},ReferredMM={1}>}ActualValue)) + (Sum({\$<[PallCare Paeds2]={1},ReferredMM={1}>}ActualValue)), '#,##0'),

num(Sum({\$<[PallCare Paeds2]={1},ReferredMM={1}>}ActualValue), '#,##0'),

num(Sum({\$<[PallCare Paeds1]={1},ReferredMM={1}>}ActualValue), '#,##0'),

num((Sum({\$<[PallCare Paeds1]={5},ReferredMM={1}>}ActualValue)) + (Sum({\$<[PallCare Paeds2]={5},ReferredMM={1}>}ActualValue)) +(Sum({\$<[PallCare Paeds2]={72},ReferredMM={1}>}ActualValue)), '#,##0'),

num(Sum({\$<[PallCare Paeds2]={5,72},ReferredMM={1}>}ActualValue), '#,##0'),

num(Sum({\$<[PallCare Paeds1]={5},ReferredMM={1}>}ActualValue), '#,##0')))