Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Creator

## Filtered the data by customer and count shows in KPI

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
Labels (6)

• ### Visualization

8 Replies
Master

Hi, like this possibly.

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

Creator
Author

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
Master

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

Creator
Author

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 ?

Master

This should work out.

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

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))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Creator
Author

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,

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,

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

Thanks.

Champion III

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,
CustomerCode & ReceiptNo as Key1  ///////joining key
;

Main:
,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:
,PostingDate
Resident Customer;

Parts:
ResponseDate,
PartsCount,
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 ))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Tags
Community Browser