Set Analysis to identify Excluded Values

[amars]

Hi all,

I have a requirement where the user wish to select a date range using a date picker object. After which I need to display the list of excluded values count and details. The transaction table looks as below

Customer_MST:
LOAD * INLINE [
CustomerID, CustomerName, Country
1, ABC, USA
2, PQR, China
3, XYZ, Russia
4, HIJ, India
5, STU, Spain
6, EFG, Portugal
];

Transaction_Tbl:
LOAD * INLINE [
InvoiceNo, CustomerID_Inv, InvoiceDt, Amount
1000, 2, 1/1/2020, 50
1001, 3, 12/1/2020, 150
1002, 2, 15/1/2020, 350
1004, 4, 18/1/2020, 250
];

Can somebody please help me identify the count of the excluded CustomerID's?

Thanks in advance

[tresesco]

Try like:

=Count( {1-$} Distinct CustomerID )

[amars]

Thanks a lot Tresesco, wondering how can I modify the expression so it will respect all the other field selections except Invoice Date.

[tresesco]

This?

=Count( {1 - <CustomerID=$::CustomerID>} Distinct CustomerID )

[amars]

Sorry Tresesco , if I am confusing.

What I want is if nothing is selected, records in customer master where there is no record in invoice Table

i.e. Customer ID 1, 5, 6

If some Invoice Date i.e. 1/1/2020 is selected all Customer ID (Except 2 for the date) should be displayed.

Please find attached a sample application. Also kindly note that the CustomerID in both the tables is referred differently.

Thanks a lot for the kind help ... 

[tresesco · Accepted solution]

=Count( {1-<CustomerID=p(CustomerID_Inv)>} Distinct CustomerID )

This should work.

[amars]

Thanks a lot Tresesco, working exactly as we need....  😊