Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
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

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.