topic if Condition with Set Analysis in QlikView

if Condition with Set Analysis

brijeshvma — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I Have a Simple Requirement , Where i Have a Product A and B ... My requirement is if sum of Product A is greater that B Than Count no of Branch,

Tab1:

Load * inline [

Branch,Product,sale,Sale1

a,A,100,200

b,A,200,120

c,B,300,230

d,A,400,401

d,B,401,500

e,A,500,497

f,A,501,203

];

i am Using a Expression as in text object

=Count(if(sum({<Product={'A'}>}sale)>=sum({<Product={'A'}>}sale1),Branch))

But i am getting an error that nested Aggregation not Allowed..

Output should be 5.

As well i am attaching an qvw.

Clever_Anjos — Mon, 26 Sep 2016 20:06:29 GMT

Please try this:

=Count(aggr(if(sum({<Product={'A'}>}sale)>=sum({<Product={'A'}>}sale1),

Branch),Branch))

sunny_talwar — Mon, 26 Sep 2016 20:06:46 GMT

Is the output 5 or 4?

=Count(Aggr(If(sum({<Product={'A'}>}sale)>=sum({<Product={'A'}>}Sale1), Branch), Branch)

Shouldn't it be just a, b, e, and f?

Anonymous — Mon, 26 Sep 2016 20:09:49 GMT

Change the order of your Expression

=if (sum({<Product={'A'}>}sale)>=sum({<Product={'A'}>}sale1), count(Branch))

sunny_talwar — Mon, 26 Sep 2016 20:11:27 GMT

In fact shouldn't it be just 3? Try this

=Count({<Branch = {"=Sum({<Product={'A'}>}sale) >= Sum({<Product={'A'}>}Sale1)"}>} Branch)

Clever_Anjos — Mon, 26 Sep 2016 20:17:33 GMT

=Sum(Aggr(if(sum({<Product={'A'}>}sale)>=sum({<Product={'A'}>}sale1),1,0),Branch))

Please don´t consider my last answer