Multiple if expression with greater than and less than

[patrickruy]

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

[sunny_talwar]

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

[marcus_sommer]

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]

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]

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