topic Re: Display Fields not related to the Selections in QlikView

Display Fields not related to the Selections

Anonymous — Tue, 21 Nov 2017 08:44:22 GMT

Hi Qlik Community,

I am currently developing an application that analyzes customer behavior. And part of this analysis is to show customers that does not by a certain product. Right now, I am currently looking for a way on how can I display the list of customers in a straight table that does not buy the selected product in the filter. So far, I've tried using if condition and E() function in Set analysis, however I cannot achieve my desired goal. I know Qlik has the power of gray that displays the unrelated data in the filter colored gray, however, in my actual data, I have so many customers which makes it hard to scroll for the unrelated customers at one glance.

Attached is an excel file containing the Sample Data in the "DATA" sheet and my desired result in "Expect Result" sheet. Also, I have attached my Sample QVW file for the solutions I've tried.

Hope you can help me achieve my goal. Thank you in advance and more power to the Qlik Community!

Re: Display Fields not related to the Selections

antoniotiman — Tue, 21 Nov 2017 09:07:16 GMT

May be this

sum({1<CustomerID=E({<ProductID=P()>})>} Sales)

Re: Display Fields not related to the Selections

Anonymous — Wed, 22 Nov 2017 01:14:36 GMT

Hi Antonio,

I've tried the solution that you provided however it does not generated my expected result.

The result of the set analysis you provided looks like below:

My expected result looks like this:

Re: Display Fields not related to the Selections

antoniotiman — Wed, 22 Nov 2017 06:40:52 GMT

Your Data Model isn't correct

Re: Display Fields not related to the Selections

Anonymous — Wed, 22 Nov 2017 07:44:55 GMT

may be try this as calculated dimension

sum( {1-$<Customers = E({1<Products={''}>})>} Sales )

Re: Display Fields not related to the Selections

stabben23 — Wed, 22 Nov 2017 08:10:01 GMT

this will help you Connect you data:

LOAD ProductID,
Products,
Price
FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

LOAD Customers,
CustomerID
FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

LOAD CustomerID1 as CustomerID, //add key to customer
ProductID1 as ProductID, //add key to product
Quantity,
Sales
FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Re: Display Fields not related to the Selections

Anonymous — Thu, 23 Nov 2017 07:13:47 GMT

Hi All,

Based on checking, my model is correct. I just don't know why the result from what I've attached has incorrect data set. However, for your reference, here what my model looks like:

Moving forward, just to give an overview regarding my expected result here's what it looks like:

As you can see in the above image, what I want is when I filter Products = "Product4", my result should be Customer1 and Customer6. Because as you can see, in my data, Customer 2, 3, 4, and 5 bought Product 4 while only Customer 1 and 6 did not purchase product 4.

Re: Display Fields not related to the Selections

woshua5550 — Thu, 23 Nov 2017 08:09:48 GMT

Hi check this

Re: Display Fields not related to the Selections

antoniotiman — Thu, 23 Nov 2017 08:19:56 GMT

My expression should work

Re: Display Fields not related to the Selections

Anonymous — Fri, 24 Nov 2017 05:38:53 GMT

may be try this as calculated dimension

sum( {1-$<Customers = E({1<Products={''}>})>} Sales )