How to handle double-counting in a many-to-one relationship (Venn diagram?)

[#1]

Hi all. So say I have a data set with two tables, products and markets. A product can be in multiple markets. So say I have a segmented bar chart, where I want to see which markets each product is in. If a product is in multiple markets, that product will be double counted, even if I use count(distinct). Ideally, I would like to see a sort of venn diagram where I can see the overlap. I'd like to see where products are only in one market, and if they exist in more than one, I want to see that intersection. Is something like this possible? Thanks for the help!

[hic]

There is no support for Venn diagrams in QlikView. But what you describe can usually still be calculated.

First of all – if you use the distinct clause, e.g. Count(distinct ProductID), then QlikView will not count the same product twice. If you use ‘Market’ as dimension, it will show the number of products per market – so the same product appears on several lines – but the total will not be the sum of the lines. Instead it will be the count of products over all markets. See picture.

The problem is the segmented bar chart. If a product is counted in several segments and the segments are stacked, then there is no other way to display the data. The bar will become too high. Use a grouped bar chart?

If you want to see the intersection, then you can always use an Aggr-function as calculated dimension or as values in a list box, e.g. aggr(Count(distinct Product),Market). Then you will get number of products in all markets as clickable values. And then you can click on ‘2’ to get the markets where you have sold 2 products. See attachment.

[#3]

Thanks for the response Henric. So I had another idea. Since my Market dimension only has 3 values, and each product can be in any of the three markets, couldn't I just create a new dimension that's like Market A, Market B, Market C, Market A+B, A+C, B+C, and A+B+C?

How would I go about doing that?

Thanks again.

[hic · Accepted solution]

I think that's a very good idea. Try to use a calculated dimension

=Aggr(distinct Concat(distinct Market,'+'),Product)

If you haven’t worked with the Aggr function before, then just think of it as an array. In this case it generates one value (the Concat function) for each value of Product. And then the chart is calculated with this as dimension. See also the bar chart to the right in the attachment.

[#5]

Ok awesome. I think I got it. Thank you so much for your help!!!