Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
truppell
Contributor

Show values that occur multiple times

Hello,

This is going to be a bit complicated to explain, so please bear with me! I am basically trying to find the intersection based on my filters.

I have a set expression that returns a number of items depending on the filter selections. I would like to add another layer of processing to exclude some items.

If I have selected/filtered for 2 values, I want only items that appear in my set 2 times.

If I have selected/filtered for 3 values, I want only items that appear in my set 3 times.

There is no limit to the number of values the user can select. It will likely often be 20 or so.

Is this possible? Thanks!

Tags (1)
1 Solution

Accepted Solutions

Re: Show values that occur multiple times

Can you try this:

To display food:

=Concat(DISTINCT {<food = {"=Count(DISTINCT name) = Count(TOTAL DISTINCT name)"}>} food, ', ')

To count food:

=Count(DISTINCT {<food = {"=Count(DISTINCT name) = Count(TOTAL DISTINCT name)"}>} food)

4 Replies

Re: Show values that occur multiple times

Would you be able to share what you have right now? and if possible a sample?

truppell
Contributor

Re: Show values that occur multiple times

I've put together a simple sample that I think will demonstrate my main question.

Data: A group of people and what they ate for lunch. Given any combination of people (filter pane), what are the dishes they had in common?

LOAD * inline [

  name, food

    Adam, salad

    Barb, soup

    Carl, salad

    Diane, salad

    Earl, soup

    Adam, veggie sandwich

    Barb, ham sandwich

    Carl, veggie sandwich

    Diane, ham sandwich

    Earl, veggie sandwich

    Adam, apple pie

    Barb, cake

    Carl, fruit

    Diane,apple pie

    Earl, fruit

]

So

  • if I select Adam & Carl, I should report salad and veggie sandwich
  • if I select Adam & Diane, I should report salad and apple pie
  • if I select Adam, Carl & Diane, I should only report salad

Now, the second part of this is that instead of a table to count from, I actually have a set statement that I'm counting from. Does that make a difference?

Re: Show values that occur multiple times

Can you try this:

To display food:

=Concat(DISTINCT {<food = {"=Count(DISTINCT name) = Count(TOTAL DISTINCT name)"}>} food, ', ')

To count food:

=Count(DISTINCT {<food = {"=Count(DISTINCT name) = Count(TOTAL DISTINCT name)"}>} food)

truppell
Contributor

Re: Show values that occur multiple times

Thanks again, Sunny!