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: 
Not applicable

Between a range of Positive and Negative

Hi,

I want to compare if a value is falling under Positive or Negative

For Example:

Target_Table:

Country    Target     Month

ABC           125          Jan

ABC           136          Feb

Compare_Table:

Country        Value    Month

ABC              100       jan

DEF               150      Feb

GHI                200      March

So if values is in the range of positive/Negative for a particular month/Year.

E.g- If the value of ABC in between +100 to -100 of Target, its true else False.. In the above table For country ABC, Target is 125 for Jan which is between +100 to -100. So it's True.

Thanks in advance.

8 Replies
Not applicable
Author

Tried with IF(num(sum(Target),'##,##0;(#,##0)')<=Value or num(sum(Target),'##,##0;(#,##0)')>= -Value, True,False)

Not getting what exactly I'm looking for though.

Any comments?

Target_Table:

Country    Target     Month

ABC           125.12345          Jan

ABC           136.23465          Feb

Compare_Table:

Country        Value    Month

ABC              100       jan

DEF               150      Feb

GHI                200      March

Thanks in advance.

vishsaggi
Champion III
Champion III

Can you explain a little more what you trying to expect the output. And if you run these two tables synthetic will be formed. However, can i know on what basis that table1 target 125 falls between 100 and -100. You mean 125 >= -100 and 125 <= 100 ? It will be false here? Correct me ?

Not applicable
Author

Hi,

Thanks for the reply.

Both the tables I gave above are for reference and are sample ones.

I want to compare Sum(Target) for a month and plant with the Value in Compare_Table.

Let's say for Jan month, ABC plant has Target value as 125.12345. So if this value falls in the range between Negative or positive of 100 I.e from Compare_Table it'll be considered as Pass else Fail.. In the above scenario 125.12345 is not in the range of +100 to -100. So it's Fail.

Thanks in advance.

boopeshj
Partner - Creator II
Partner - Creator II

Hi gtripathy

It worked for me.

if( (sum(Target) < sum(Value)and sum(Target) > (-1 * sum(Value))), 'True' , 'False') . Let me know if any concern.

Regards

Boopesh

Please mark correct if you find them

trial true or false.PNG

vishsaggi
Champion III
Champion III

Can you try this ?

Not applicable
Author

Sorry for the late reply.

IF(num(sum(Target),'##,##0;(#,##0)')<=num(sum(Value),'##,##0;(#,##0)' )AND num(sum(Target),'##,##0;(#,##0)')> -num(sum(Value),'##,##0;(#,##0)') True,False)


Else Tweaking what Boopesh posted.


Thanks again.

vishsaggi
Champion III
Champion III

Did you resolve your issue if not can you post the expected output you are looking for ?

Not applicable
Author

Yes it was resolved.. Thanks again.