Filtered the data by customer and count shows in KPI

[Sara_3]

Requirements:

1- Expected data in table 

2- Parts Count in KPI

For Example : below Table has 3 Customer codes, I need distinct Customer code with its Receipt number where Min Posting Date is greater than Min Response Date with its Parts Count. Data is coming from different Tables in Data Model.

Given Data:

Customer Code	Receipt No	Posting Date	Response Date	Parts Count
101	001	4/4/2020	5/7/2019	4
101	002	5/9/2021	6/10/2022	6
101	003	8/2/2020	3/5/2021	10
203	100	3/2/2022	3/2/2023	20
203	203	2/9/2021	6/5/2022	22
203	010	5/6/2019	1/2/2019	16
203	211	9/2/2021	8/7/2022	8
203	305	5/8/2019	6/5/2021	10
355	008	5/6/2019	3/2/2019	10
355	222	8/10/2021	5/6/2022	28
355	325	6/4/2020	8/7/2021	32
355	900	3/28/2020	10/12/2020	64

1- Expected/ Result Data: in the above table mentioned in RED.

2- Expected Parts Count in KPI = 4+16+10 = result is  30 

Customer Code	Receipt No	Posting Date	Response Date	Parts Count
101	001	4/4/2020	5/7/2019	4
203	010	5/6/2019	1/2/2019	16
355	008	5/6/2019	3/2/2019	10

[BrunPierre]

Hi, like this possibly.

=Sum(Aggr(If(Min([Posting Date]) > Min([Response Date]),[Parts Count]), [Customer Code], [Receipt No]))

[Sara_3]

Hi, 

Thanks, I tried your expression it works perfectly with the above given Customer code , I have another Customer Code which has same Min Posting Date within a Receipt no , example below in the table:

for the below scenario your expression did not add 14 and 4  in SUM, according to our logic the 4 should be add in SUM because it has Min Posting Date and Min Response Date. How to fix it, if we have same Min Posting Date within Receipt NO? 

Customer Code	Receipt No	Posting Date	Response Date	Parts Count
405	101	4/2/2019	1/10/2019	14
405	101	4/2/2019	9/27/2018	4

[BrunPierre]

=Sum(If([Response Date] = Aggr(NODISTINCT Min([Response Date]), [Customer Code]), [Parts Count]))

[Sara_3]

it is not working it pulls additional Parts count of each Receipt Nos. for example expected SUM all 4 Customer code's Parts count like 4, 16 , 10 and 4  = SUM = 34 . it gave me double or triple or few are correct.

is it possible I can only pull min Response Date data in the script ?

[BrunPierre]

This should work out.

---

=Sum(If([Response Date] = Aggr(NODISTINCT Min([Response Date]), [Customer Code]), [Parts Count]))

[vinieme12]

Try below

sum(aggr(if(ResponseDate<PostingDate,PartsCount),CustomerCode,ReceiptNo,PostingDate,ResponseDate))

You can replace the underlined part with a field that identifies each record combination uniquely

sum(aggr(if(ResponseDate<PostingDate,PartsCount),keyfield1,keyfield2))

[Sara_3]

Hi Vineeth,

I have got most of the expected data by this expression : 

=Sum(Aggr(If(Min([Posting Date]) > Min([Response Date]),[Parts Count]), [Customer Code], [Receipt No]))

the problem is when Customer Code's Receipt no has same Min Posting Date it is not going to add into SUM Parts count. here I am going to attached the excel sheet . Excel sheet consist of :

1- Full data of 3 Customer Code with multiple Receipts No , highlighted BLUE ones (expected data I got) and RED ones (not getting because of same min Posting Dates)

2- in the bottom of the excel sheet I created the 3 tables of each Customer code of expected result.

I am using 2 Tables to get the fields , Tables are:

Customer:

Customer Code,

Receipt No,

Posting Date,

Customer Code & Receipt No as Key1 ;    ///joining key

Parts:

Response Date,

Parts Count,

Deadline Date,

Creation Date,

Hi Vineeth,

I got most of the expected data by this expression : 

=Sum(Aggr(If(Min([Posting Date]) > Min([Response Date]),[Parts Count]), [Customer Code], [Receipt No]))

the problem is when Customer Code's Receipt no has same Min Posting Date it is not going to add into SUM Parts count. here I am going to attached the excel sheet . Excel sheet consist of :

1- Full data of 3 Customer Code with multiple Receipts No , highlighted BLUE ones (expected data I got) and RED ones (not getting because of same min Posting Dates)

2- in the bottom of the excel sheet I created the 3 tables of each Customer code of expected result.

I am using 2 Tables to get the fields , Tables are:

Customer:

Customer Code,

Receipt No,

Posting Date,

Customer Code & Receipt No as Key1;       ///////joining key

Parts:

Response Date,

Parts Count,

Deadline Date,

Customer Code & Receipt No as Key1;   ///////joining key

Thanks.

[vinieme12]

you need Aggr() on all fields that help to identify each record uniquely

which is why you need to add all fields to Aggr() as below

sum(aggr(if(ResponseDate<PostingDate,PartsCount),CustomerCode,ReceiptNo,PostingDate,ResponseDate))

using Min() can return incorrect results based on your data as it will aggregate results only at Receipt level and not Posting and Response date combination

You can consolidated the two tables by JOINING them and then create a new unique identifier key field

temp:
CustomerCode,
ReceiptNo,
PostingDate,
CustomerCode & ReceiptNo as Key1;       ///////joining key

Left Join(temp)
ResponseDate,
PartsCount,
DeadlineDate,
CustomerCode & ReceiptNo as Key1  ///////joining key
;

Main:
Load *
,CustomerCode&'-'&ReceiptNo&'-'&PostingDate&'-'&ResponseDate  as UniqueKey
 Resident temp;
Drop table temp;

If you are unable to join the datasets then you can map the Posting Date to Parts table

Customer:
CustomerCode,
ReceiptNo,
PostingDate,
CustomerCode & ReceiptNo as Key1;       ///////joining key

Map_PostingDate:
Mapping Load
Load Key1
,PostingDate
Resident Customer;


Parts:
ResponseDate,
PartsCount,
DeadlineDate,
CustomerCode & ReceiptNo as Key1  ///////joining key
,CustomerCode&'-'&ReceiptNo&'-'& Applymap('Map_PostingDate',CustomerCode&ReceiptNo) &'-'&ResponseDate  as UniqueKey
;

With the UniqueKey expression use below expression

=sum(aggr(if(ResponseDate<PostingDate,PartsCount),UniqueKey ))