Hi
I have an expression that I was using to bring in target data for a table and it worked fine:
if(ISNULL(
NUM(AVG( {$<TargetCode={'CPA27'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={6}, ReferredMM=, YearMonth=>} [AnnualTarget]), format.percentage.decimal1)
), NULL(),
NUM(AVG( {$<TargetCode={'CPA27'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={6}, ReferredMM=, YearMonth=>} [AnnualTarget]), format.percentage.decimal1))
However I need to change it so that I can hard code targets for specific Service Provider numbers and have the code in below which is bringing in the correct data but also showing up hospitals that are blank that were excluded with the first code
IF(ISNULL(
if(ServiceProviderNum='5099','≤5.0',
if(ServiceProviderNum='5006','≤6.2',
if(ServiceProviderNum='5004','≤4.2',
if(ServiceProviderNum='5007','≤5.4',
if(ServiceProviderNum='5005','≤5.3',
if(ServiceProviderNum='5008','≤4.6',
if(ServiceProviderNum='5002','≤4.1',
NUM(AVG( {$<TargetCode={'CPA27'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={6}, ReferredMM=, YearMonth=>} [AnnualTarget]), format.percentage.decimal1))))))))), NULL(),
if(ServiceProviderNum='5099','≤5.0',
if(ServiceProviderNum='5006','≤6.2',
if(ServiceProviderNum='5004','≤4.2',
if(ServiceProviderNum='5007','≤5.4',
if(ServiceProviderNum='5005','≤5.3',
if(ServiceProviderNum='5008','≤4.6',
if(ServiceProviderNum='5002','≤4.1',
'≤' & NUM(AVG( {$<TargetCode={'CPA27'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={6}, ReferredMM=, YearMonth=>} [AnnualTarget]), format.percentage.decimal1)))))))))
any help on how to exclude the service Provider numbers that are blank would be great. I have exclude nulls in the dimensions already
Thanks
