Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to display "join residuals" in tables

Hi everybody,

I would like to visualise the result of an analysis where I compare sets of products offered by a company and the demand of customers for these products. The idea is that the user can explore different matches for specific customers in the sense of "If I offer this customer a certain set or a combination of sets, will their needs be satisfied?"

The input tables look like this:

tables_mock.PNG

And the results like this. There are three potential "buckets" for a product:

results_mock.PNG

I would like to visualise the result buckets as simple tables. Then on top of these tables two filter panes, one for 'Set configurations' one for 'Users'.

Thank you very much for your help!

1 Solution

Accepted Solutions
Not applicable
Author

Guys,

thanks a lot for your efforts!

In the end I solved it with an adaption of swuehl's logic:

Issue was that 1) (the "left" table) didn't work as I wished for. It didn't react correctly when selecting a Customer or Set.

So what I did is to negate statement 3) and use it as input for table 1.

So the final statements are:

Table 1: Products w/ no demand

Dimension: Product

Measure: Count({1<Product = p({<Set *= {"*"},Customer= >}) - p({<Customer *= {"*"},Set= >}) >} Product)

Table 2: Products that have demand

Dimension: Product

Measure: Count({1<Product = p({<Customer *= {"*"} >})* p({<Set *= {"*"} >}) >} Product)

Table 3: Unfullfilled customer needs

Dimension: Product

Measure: Count({1<Product = p({<Customer *= {"*"},Set= >}) - p({<Set *= {"*"},Customer= >}) >} Product)

Again, thank you everybody, this was huge

David

View solution in original post

10 Replies
Not applicable
Author

To clarify, I'm looking for code to put in the "Dimension" field of a simple tamble in order to visualise the 3 "bucket tables" shown above.

I tried to work with isnull() statements but did not succeed.

Thank you!

marcus_sommer

Maybe someone could help you if you explained in more detail how these buckets should be created and also how these tables are related.

- Marcus

sunny_talwar

Like this?

Capture.PNG

Although, I must confess the set analysis for each of them was a trial and error because for some reason they weren't behaving properly. Especially the last list box (Unfulfilled customer needs) was random. I think we need more guidance from marcus_sommer‌ or swuehl‌ to make sure that

1) these sets are doing what you need

2) if there are betters ways to do this

3) why was Unfulfilled not working with just Aggr(, Product) and needed Customer, Set as dimensions where as Product w/no demand worked without Customer, Set.

UPDATE: Just so other's who cannot download the file can view the expression:

1) =Aggr(Only({1<Customer = {'*'}>/1<Set = {'*'}>}Product), Product)

2) =Aggr(Only({1<Customer = {'*'}>*1<Set = {'*'}>}Product), Product)

3) =Aggr(Only({<Customer = {"=NullCount(Set) > 0"}> - (1<Customer = {'*'}>*1<Set = {'*'}>)}Product), Product, Customer, Set)

Best,

Sunny

marcus_sommer

Hi Sunny,

great solution - I hope your guess on the requirements was right. But without clarifying how the logic should work and the datamodel looks like I'm not sure.

- Marcus

sunny_talwar

Marcus -

Based on my understanding the logic is like this

List Box 1 - Product exists in Set Configuration Table, but doesn't exist in customer needs table

List Box 2 - Product exists in both the tables

List Box 3 - Product exists in customer needs, but not in set configuration.

Back end solution can be very easy using Where Exists, but front end became a little complicated

swuehl
MVP
MVP

I would use a slightly different approach for the set analysis, something like

{1<Product = e({<Customer *= {"*"},Set= >}) >}

{1<Product = p({<Customer *= {"*"} >}) * p({<Set *= {"*"} >}) >}

{1<Product = p({<Customer *= {"*"},Set= >}) - p({<Set *= {"*"},Customer= >}) >}

CustomerSet.png

sunny_talwar

But should those list or table boxes be filtering when made selection? I thought they would need to act like a list box for selection. I guess that is something the OP needs to decide.

Do you know why the last list box needed Customer and set as dimension Stefan?

swuehl
MVP
MVP

As I understood the OP, he want the tables to react to selections in Customer and / or Set:

"The idea is that the user can explore different matches for specific customers in the sense of "If I offer this customer a certain set or a combination of sets, will their needs be satisfied?"


I think your last list box only needs Product and Customer as dimensions (not all three fields), not really sure why using Product only does not work. It's always harder for me to trace back set analysis with operators working on record sets.


Note that all my three list list boxes only require a single aggr() dimension Product.


David, please note that my middle table should have a caption 'Products with demand', this is a copy & paste error.

Not applicable
Author

Guys,

thanks a lot for your efforts!

In the end I solved it with an adaption of swuehl's logic:

Issue was that 1) (the "left" table) didn't work as I wished for. It didn't react correctly when selecting a Customer or Set.

So what I did is to negate statement 3) and use it as input for table 1.

So the final statements are:

Table 1: Products w/ no demand

Dimension: Product

Measure: Count({1<Product = p({<Set *= {"*"},Customer= >}) - p({<Customer *= {"*"},Set= >}) >} Product)

Table 2: Products that have demand

Dimension: Product

Measure: Count({1<Product = p({<Customer *= {"*"} >})* p({<Set *= {"*"} >}) >} Product)

Table 3: Unfullfilled customer needs

Dimension: Product

Measure: Count({1<Product = p({<Customer *= {"*"},Set= >}) - p({<Set *= {"*"},Customer= >}) >} Product)

Again, thank you everybody, this was huge

David