Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
amars
Specialist
Specialist

Set Analysis to identify Excluded Values

 

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

 

 

 

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

 

This should work.

View solution in original post

6 Replies
tresesco
MVP
MVP

Try like:

=Count( {1-$} Distinct CustomerID )

amars
Specialist
Specialist
Author

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

tresesco
MVP
MVP

This?

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

amars
Specialist
Specialist
Author

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

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

 

This should work.

View solution in original post

amars
Specialist
Specialist
Author

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