topic expression using dual in QlikView

expression using dual

rcorcoran — Wed, 22 Aug 2018 13:06:09 GMT

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

))))))))

Re: expression using dual

Gysbert_Wassenaar — Wed, 22 Aug 2018 13:09:34 GMT

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.

Re: expression using dual

rcorcoran — Wed, 22 Aug 2018 13:17:52 GMT

Hi Gysbert

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

Re: expression using dual

Gysbert_Wassenaar — Wed, 22 Aug 2018 13:53:57 GMT

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

Re: expression using dual

BalaBhaskar_Qlik — Wed, 22 Aug 2018 16:08:17 GMT

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

))))))))

Re: expression using dual

BalaBhaskar_Qlik — Wed, 22 Aug 2018 16:09:25 GMT

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

))))))))

Re: expression using dual

rcorcoran — Thu, 23 Aug 2018 10:10:37 GMT

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