expression using dual

[rcorcoran]

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))

))))))))

[Gysbert_Wassenaar]

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.

[rcorcoran]

Hi Gysbert

I did have and and have now replaced again to and but it is still not working

[Gysbert_Wassenaar]

Can you post a small qlikview document that demonstrates the problem?

[balabhaskarqlik]

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))

))))))))

[balabhaskarqlik · Accepted solution]

=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))

))))))))

[rcorcoran]

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