Advance Set analysis help

[#1]

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‌

[vinieme12]

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

Try below

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

[#3]

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)

[vinieme12]

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)

[MK_QSL]

You can also use this..

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

[MK_QSL]

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,    10, 100,    A

  2014-08-10,    25, 100,    B

  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)

[MK_QSL]

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 ...!!

[vinieme12]

Yes, the same aggregation can be achieved with

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

[sunny_talwar]

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