Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
rcorcoran
Creator
Creator

expression using dual

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

))))))))

1 Solution

Accepted Solutions
balabhaskarqlik

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

))))))))

View solution in original post

6 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand
rcorcoran
Creator
Creator
Author

Hi Gysbert

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

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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


talk is cheap, supply exceeds demand
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

=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
Creator
Creator
Author

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