Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have the expression below that I need to use for a target. it works for the initial piece without the second part of it where I have the service Provider numbers <> etc but not for the whole lot. Any help appreciated
=if(ServiceProviderNum='5099',DUAL('<=' &'5.0','5.0'),
if(ServiceProviderNum='5006',DUAL('<=' &'6.2','6.2'),
if(ServiceProviderNum='5004',DUAL('<=' &'4.2','4.2'),
if(ServiceProviderNum='5007',DUAL('<=' &'5.4','5.4'),
if(ServiceProviderNum='5005',DUAL('<=' &'5.3','5.3'),
if(ServiceProviderNum='5008',DUAL('<=' &'4.6','4.6'),
if(ServiceProviderNum='5002',DUAL('<=' &'4.1','4.1'),
if(ServiceProviderNum<>'5099' or ServiceProviderNum<>'5006' or ServiceProviderNum<>'5004' or ServiceProviderNum<>'5007'
and ServiceProviderNum<>'5005' or ServiceProviderNum<>'5008' or ServiceProviderNum <>'5002',
DUAL('<=' & NUM(AVG( {$<TargetCode={'CPA12'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>} [Target]),
AVG( {$<TargetCode={'CPA12'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>} [Target]), format.decimal1))
))))))))
=if(ServiceProviderNum='5099',DUAL('<=' &'5.0','5.0'),
if(ServiceProviderNum='5006',DUAL('<=' &'6.2','6.2'),
if(ServiceProviderNum='5004',DUAL('<=' &'4.2','4.2'),
if(ServiceProviderNum='5007',DUAL('<=' &'5.4','5.4'),
if(ServiceProviderNum='5005',DUAL('<=' &'5.3','5.3'),
if(ServiceProviderNum='5008',DUAL('<=' &'4.6','4.6'),
if(ServiceProviderNum='5002',DUAL('<=' &'4.1','4.1'),
If(Not (Match(ServiceProviderNum,'5099','5006','5004','5007','5005','5008','5002')),
DUAL('<=' & NUM(
AVG( {$<TargetCode={'CPA12'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>} [Target]),
AVG( {$<TargetCode={'CPA12'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>} [Target]), format.decimal1))
))))))))
Conditions like this will always be true: ServiceProviderNum<>'5099' or ServiceProviderNum<>'5006. A single ServiceProviderNum will always be unequal to one of two different values. I think you want to replace the or's with and's.
Hi Gysbert
I did have and and have now replaced again to and but it is still not working
Can you post a small qlikview document that demonstrates the problem?
May be this:
=if(ServiceProviderNum='5099',DUAL('<=' &'5.0','5.0'),
if(ServiceProviderNum='5006',DUAL('<=' &'6.2','6.2'),
if(ServiceProviderNum='5004',DUAL('<=' &'4.2','4.2'),
if(ServiceProviderNum='5007',DUAL('<=' &'5.4','5.4'),
if(ServiceProviderNum='5005',DUAL('<=' &'5.3','5.3'),
if(ServiceProviderNum='5008',DUAL('<=' &'4.6','4.6'),
if(ServiceProviderNum='5002',DUAL('<=' &'4.1','4.1'),
If(Not (Match(ServiceProviderNum,5099,5006,5004,5007,5005,5008,5002)),
DUAL('<=' & NUM(
AVG( {$<TargetCode={'CPA12'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>} [Target]),
AVG( {$<TargetCode={'CPA12'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>} [Target]), format.decimal1))
))))))))
=if(ServiceProviderNum='5099',DUAL('<=' &'5.0','5.0'),
if(ServiceProviderNum='5006',DUAL('<=' &'6.2','6.2'),
if(ServiceProviderNum='5004',DUAL('<=' &'4.2','4.2'),
if(ServiceProviderNum='5007',DUAL('<=' &'5.4','5.4'),
if(ServiceProviderNum='5005',DUAL('<=' &'5.3','5.3'),
if(ServiceProviderNum='5008',DUAL('<=' &'4.6','4.6'),
if(ServiceProviderNum='5002',DUAL('<=' &'4.1','4.1'),
If(Not (Match(ServiceProviderNum,'5099','5006','5004','5007','5005','5008','5002')),
DUAL('<=' & NUM(
AVG( {$<TargetCode={'CPA12'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>} [Target]),
AVG( {$<TargetCode={'CPA12'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>} [Target]), format.decimal1))
))))))))
Hi Bala
thank you so much that worked I just had the decimal variable in the wrong place above but changed that too and all ok
Rhona