Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for
Did you mean:
Partner - Contributor II

## Intersection of dimensional values

Hi. What is the best solution for getting the intersection of two dim-field values in a table?

Lets say we have a order-id (connected to a customer) and one order-id can have several orderline-id (same customer buying several products results in order-id with several orderlines).

Order-id         orderline-id      productlevel_1   product_level_2
1                        ab                         ice-cream              ice cream with banana
1                       abc                       chocolate             Chocolate-cake
2                    abcd                        ice cream               ice-cream with apple
3                   efg                             ice-cream              ice cream with apple
3                   fgh                           chocolate              chocolate-candy

In this case I want to count the intersect of everybody having bought both ice-cream and chocolate (prod_level_1) (but not only chocolate or only ice cream). It should give 2 in this case (order id 1 and 3).

I have tried a few ways:

1. Gives result but probably not correct results?

sum(aggr(count({< product_level_1= {'ice cream'}>} product_level_1) * count({< product_level_1 = {'Chocolate'}>}product_level_1), order_id))

2. Gives 0.
count({<product_level_1 *= {'Chocolate,'ice cream'}>}distinct order_id)

3. Gives 0.
count({<product_level_1 = {'Chocolate'}>*<product_level_1 ={'ice cream'}>}distinct order_id)

If you have suggestions on how to solve it in SQL or in the script in a better way thats fine too. I also want to calculate the ones who only buy ice cream but not chocolate.

Labels (1)
• ### Set Analysis

1 Solution

Accepted Solutions

ice-cream and chocolate:

=Count(
{<[Order-id] = P({<productlevel_1= {'ice-cream'}>})>
* <[Order-id] = P({<productlevel_1= {'chocolate'}>})>}

DISTINCT [Order-id])

ice-cream and not chocolate:

=Count(
{<[Order-id] = P({<productlevel_1= {'ice-cream'}>})>
* <[Order-id] = E({<productlevel_1= {'chocolate'}>})>}

DISTINCT [Order-id]

2 Replies

ice-cream and chocolate:

=Count(
{<[Order-id] = P({<productlevel_1= {'ice-cream'}>})>
* <[Order-id] = P({<productlevel_1= {'chocolate'}>})>}

DISTINCT [Order-id])

ice-cream and not chocolate:

=Count(
{<[Order-id] = P({<productlevel_1= {'ice-cream'}>})>
* <[Order-id] = E({<productlevel_1= {'chocolate'}>})>}

DISTINCT [Order-id]

Partner - Contributor II
Author

Thank you very much!