Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

patrickruy
New Contributor

Multiple if expression with greater than and less than

Hello,

I have 2 variables that I want to use in an expression. The variables contains a value and a sign. The sign is < or >.

So I have 4 options:

Option 1: Variabele 1 = > ### and  Variabele 2 = > ####

Option 2: Variabele 1 = < ### and  Variabele 2 = < ####

Option 3: Variabele 1 = > ### and  Variabele 2 = < ####

Option 4: Variabele 1 = < ### and  Variabele 2 = > ####


Now I have following expression. The first two options work but not the last two. Where did I go wrong?


= if(left(Variable1,1)= '<'  ,if(Sales)  < PurgeChar(Variable1,'<')
and left(Variable2,1)= '<'  ,if(Field_A < PurgeChar(Variable2,'<')   ,Sales)))

,
if(left(Variable1,1)= '>'  ,if(Sales)  > PurgeChar(Variable1,'>')
and left(Variable2,1)= '>'  ,if(Field_A > PurgeChar(Variable2,'>')   ,Sales)))
                                                                                                                           

,
if(left(Variable1,1)= '<'  ,if(Sales)  < PurgeChar(Variable1,'<')
and left(Variable2,1)= '>'  ,if( Field_A > PurgeChar(Variable2,'>')   ,Sales)))
                                                                                                                           

,
if(left(Variable1,1)= '>'  ,if(Sales)  > PurgeChar(Variable1,'>')
and left(Variable2,1)= '<'  ,if(Field_A < PurgeChar(Variable2,'<')   ,Sales)))
))))



4 Replies

Re: Multiple if expression with greater than and less than

Would you be able to share a sample to see the issue?Preparing examples for Upload - Reduction and Data Scrambling

MVP & Luminary
MVP & Luminary

Re: Multiple if expression with greater than and less than

It's not quite clear what do you want to do. Each result would be Sales ... Nevertheless I would try to simplify the approach by using the $-sign expansion, for example:

if(Sales) $(=left(Variable1,1)) PurgeChar(Variable1,'<>=') and

   Field_A $(=left(Variable1,2)) PurgeChar(Variable2,'<>='), ...

or maybe even with something like this:

if(Sales) $(=Variable1) and Field_A $(=Variable2), ...

- Marcus

Highlighted
patrickruy
New Contributor

Re: Multiple if expression with greater than and less than

I have a table where the end user can fill in (by example) Variable1 ">100000" and variable2 "<10" then it have to show the sales.

But the end user may also fill in Variable1 "<200000" and Variable2 "<5" and then als shows the sales.

Variable1 could be Sales and variable2 could be a certain index.

I hope this is useful. I will tomorrow look at your examples. Thanks.

patrickruy
New Contributor

Re: Multiple if expression with greater than and less than

Oh, the dimension is Customer so it shows sales per customer.