topic Between a range of Positive and Negative in QlikView

Between a range of Positive and Negative

Thu, 04 Aug 2016 06:37:52 GMT

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.

Re: Between a range of Positive and Negative

Thu, 04 Aug 2016 11:53:07 GMT

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.

Re: Between a range of Positive and Negative

vishsaggi — Thu, 04 Aug 2016 13:19:46 GMT

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 ?

Re: Between a range of Positive and Negative

Thu, 04 Aug 2016 13:46:20 GMT

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.

Re: Between a range of Positive and Negative

boopeshj — Thu, 04 Aug 2016 14:09:28 GMT

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

Re: Between a range of Positive and Negative

vishsaggi — Thu, 04 Aug 2016 14:20:13 GMT

Can you try this ?

Re: Between a range of Positive and Negative

Fri, 19 Aug 2016 13:40:39 GMT

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.

Re: Between a range of Positive and Negative

vishsaggi — Fri, 19 Aug 2016 14:24:52 GMT

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

Re: Between a range of Positive and Negative

Fri, 19 Aug 2016 17:56:10 GMT

Yes it was resolved.. Thanks again.