topic Re: Advance Set analysis help in QlikView

Advance Set analysis help

Sun, 22 Jan 2017 09:28:06 GMT

Hi All,

I have below data set

DeliveryDate	Per	SupplierCode
2014-08-10	0.2	A
2014-08-10	0.5	B
2014-08-11	0.3	B
2014-08-11	0.6	A
2014-08-12	0.2	A
2014-08-12	0.5	B

Output I need is below

DeliveryDate	Count
8/10/2014	1
8/11/2014	2
8/12/2014	1

Logic - I need to count the suppliers for particular Date, where per is greater than 0.2.

I am using below expression but it's giving wrong results

= Count({<SupplierCode={"=sum(Per)>0.2"}>} distinct SupplierCode)

gwassenaar‌ stalwar1‌ swuehl‌ kush141087‌

Re: Advance Set analysis help

vinieme12 — Sun, 22 Jan 2017 10:11:38 GMT

= Count({<DeliveryDate={"=sum(Per)>0.2"}>} distinct SupplierCode)

Try below

= Count({<Per={">0.2"}>} distinct SupplierCode)

Re: Advance Set analysis help

Sun, 22 Jan 2017 10:33:08 GMT

thanks for your reply

below is my actual data set

Data:

LOAD * Inline [

DeliveryDate, Field1,Field2, SupplierCode

2014-08-10, 20, 100, A

2014-08-10, 50, 100, B

2014-08-11, 30, 100, B

2014-08-11, 60, 100, A

2014-08-12, 20, 100, A

2014-08-12, 50, 100 , B ] ;

Per is calculated as, sum(Field1)/sum(Field2)

I cannot directly do Field1/Field2 in script because of some data complexity. So I am trying below expression, but it's not working

=Count({<SupplierCode={"=(sum(Field1)/sum(Field2))>0.2"}>} distinct SupplierCode)

Re: Advance Set analysis help

vinieme12 — Sun, 22 Jan 2017 10:53:58 GMT

You need a key that identifies each row

Data:

LOAD *,Recno() as RowKEY Inline [

DeliveryDate,Field1,Field2,SupplierCode

2014-08-10,20,100,A

2014-08-10,50,100,B

2014-08-11,30,100,B

2014-08-11,60,100,A

2014-08-12,20,100,A

2014-08-12,50,100,B

];

Expression

= Count({<RowKEY={"=(sum(Field1)/Sum(Field2))>0.2"}>} distinct SupplierCode)

Re: Advance Set analysis help

MK_QSL — Sun, 22 Jan 2017 11:45:09 GMT

You can also use this..

SUM(Aggr(IF(SUM(Field1)/SUM(Field2)>0.2,1,0),DeliveryDate,SupplierCode))

Re: Advance Set analysis help

MK_QSL — Sun, 22 Jan 2017 12:23:45 GMT

or create an ID field

Data:

LOAD

AutoNumber(DeliveryDate & SupplierCode) as ID,

Inline

[

DeliveryDate, Field1, Field2, SupplierCode

2014-08-10, 10, 100, A

2014-08-10, 25, 100, B

2014-08-11, 30, 100, B

2014-08-11, 10, 100, A

2014-08-11, 50, 100, A

2014-08-12, 20, 100, A

2014-08-12, 50, 100, B

] ;

and use below expression

COUNT({<ID = {"=SUM(Field1)/SUM(Field2)>0.2"}>}DISTINCT ID)

Re: Advance Set analysis help

MK_QSL — Sun, 22 Jan 2017 12:26:32 GMT

vinieme12‌ Good Solution.

But this will not give you correct total .. Total must be 4 but it will give you 2.

Try your expression in Text Box ...!!

Re: Advance Set analysis help

vinieme12 — Sun, 22 Jan 2017 12:33:19 GMT

Yes, the same aggregation can be achieved with

= SUM(AGGR(Count({<RowKEY={"=(sum(Field1)/Sum(Field2))>0.2"}>} distinct SupplierCode),DeliveryDate,SupplierCode))

Re: Advance Set analysis help

sunny_talwar — Sun, 22 Jan 2017 12:58:11 GMT

This was the solution I was going to recommend.... Basically create an ID field by combining those fields where you want to check for Sum(Field1/Sum(Field2) so that you can use set analysis. Otherwise Aggr() is the other option