Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nayanmistry
New Contributor II

Only show all company answer if a specific answer has been selected.

Hi,

I have encountered this issue a few times and I'm unsure how to get around it. It's difficult to explain out loud but here I go!

I have 3 columns in a table, each its own dimension: CompanyName, Question, Answers.

Say each company is asked 7 questions each, I want to be able to see all the answers of a company if they have answered a specific answer to a specific question.

Example Table:

CompanyNameQuestionAnswer
Supplier 1Do we approve:Yes
Supplier 1ProductApples
Supplier 1Type of BusinessFood
Supplier 1AreaUK
Supplier 1SizeLarge
Supplier 1InsuredYes
Supplier 1ContactJohn
Supplier 2Do we approve:Yes
Supplier 2ProductBananas
Supplier 2Type of BusinessFood
Supplier 2AreaUK
Supplier 2SizeSmall
Supplier 2InsuredNo
Supplier 2ContactBob
Supplier 3Do we approve:No
Supplier 3ProductBooks
Supplier 3Type of BusinessEducation
Supplier 3AreaEurope
Supplier 3SizeLarge
Supplier 3InsuredYes
Supplier 3ContactSally
Supplier 4Do we approve:-
Supplier 4Product-
Supplier 4Type of Business-
Supplier 4Area-
Supplier 4Size-
Supplier 4Insured-
Supplier 4Contact-

So for example, I only want to see all 6 answers for every company who have responded 'Yes' to 'Do we approve' and so the below table will be the result:

Output:

CompanyNameQuestionAnswer
Supplier 1Do we approve:Yes
Supplier 1ProductApples
Supplier 1Type of BusinessFood
Supplier 1AreaUK
Supplier 1SizeLarge
Supplier 1InsuredYes
Supplier 1ContactJohn
Supplier 2Do we approve:Yes
Supplier 2ProductBananas
Supplier 2Type of BusinessFood
Supplier 2AreaUK
Supplier 2SizeSmall
Supplier 2InsuredNo
Supplier 2ContactBob

If I were to use the expression:

If(question like 'Do we approve:' and Answer = 'Yes', CompanyName)

...then the table filters to just the two suppliers 1 and 2 and only shows their 1 question 'Do we approve' and its answer 'Yes' and doesn't show the rest of their questions and answers.

In reality, I'm using this to basically create a report where I can review the companies who have responded either 'No' or still null so the 'Yes' companies can be excluded.


Any help with my example of my real expression will be much appreciated!


THANK YOU!!!!

1 Solution

Accepted Solutions
MVP
MVP

Re: Only show all company answer if a specific answer has been selected.

Try this

Dimension

CompanyName

Question

Expression

Only({<CompanyName = p({<Question = {'Do we approve:'}, Answer = {'Yes'}>})>}Answer)


Capture.PNG

9 Replies
MVP
MVP

Re: Only show all company answer if a specific answer has been selected.

Try this

Dimension

CompanyName

Question

Expression

Only({<CompanyName = p({<Question = {'Do we approve:'}, Answer = {'Yes'}>})>}Answer)


Capture.PNG

nayanmistry
New Contributor II

Re: Only show all company answer if a specific answer has been selected.

Hi Sunny

Thank you so much for this! I was thinking set analysis would be the solution here yet I'm still new to and wrapping my head around it.

Your expression works perfectly!!!!!

nayanmistry
New Contributor II

Re: Only show all company answer if a specific answer has been selected.

Hi Sunny

I'm using your same same expression again but for a different project I'm working on. I'm just wonder if there is a way to output this expression as a filter or like have it but as a dimension??

I'll describe an example of what I'm referring to by using my same set of data above. Say for the question 'Product', it has the multichoice options:

-Apples

-Bananas

-Books

Filter option:

I want to be able to create a filter called 'Product' and then the 3 filter options will be 'Apples', 'Bananas' & 'Books' and when i click on 'Apples', the dashboard (the table within) will sync to just those companies who selected Apples (just to Supplier 1) and will then still display all their other question answers.


Dimension:

Same as the above but in a graph, each bar will be 'Apples', 'Bananas', 'Books' and the questions will crunch the height of the bars (say if these question answers can be comparable numeric values).


I hope this makes sense, any assistance will be highly appreciated or if you can point me in the right direction.


Many thanks!

Nayan

MVP
MVP

Re: Only show all company answer if a specific answer has been selected.

Not completely sure I follow, would you be able to provide the expected output (in numerical terms) for the sample you have provided above?

nayanmistry
New Contributor II

Re: Only show all company answer if a specific answer has been selected.

Hi Sunny

Please can we ignore the Dimension bit of my previous post as I think I didn't really explain it well / in hindsight, it doesn't seem relevant.

Basically, the set analysis expression you built lets me show all the answers to all the questions but only for the companies who answered a specific answer to a specific question. This works perfectly.

My next step is that I want to be able to create a filter inside the dashboard that performs in a similar way.

For the Question: 'Product:' the answers for it can be:

- Apples
- Bananas
- Books

The filter can be called 'Select Product' and the options will be the 3 above. When i click on 'Apples' for example, I'll see the table Sync to just Supplier 1 but will show me not just the their question 'Product' and their 'answer' apples, but also the other 6 questions and their answers for supplier 1 too like the set analysis expression can do.

Hopefully this now makes more sense. please let me know if not?

Thanks,

Nayan

MVP
MVP

Re: Only show all company answer if a specific answer has been selected.

Something like this?

Capture.PNG

nayanmistry
New Contributor II

Re: Only show all company answer if a specific answer has been selected.

Hi Sunny

Thank you for this! The filter expression works nicely and so does the one in the table.

I'm trying to wrap my head around the expression logic, does this seem correct:

Filter:

Aggr(Only({1<Question = {'Product'}>} Answer), Answer)

  • Aggr () = to group the whole measure by Answer.
  • Only() = only selects these answers...
  • 1 = to still represent and show all the selections.

Table Measure:

Only({<Answer, CompanyName = p(CompanyName)>}Answer)

  • Only () = Only show these answers
  • '<Answer...' we're evaluating the answer (and company name)
  • 'CompanyName = p(CompanyName)' show all the company records where these companies are possible.
MVP
MVP

Re: Only show all company answer if a specific answer has been selected.

Filter:

Aggr(Only({1<Question = {'Product'}>} Answer), Answer)

  • Aggr () = to group the whole measure by Answer.
  • Only() = only selects these answers...
  • 1 = to still represent and show all the selections.

Well this is just how calculated dimension work.... I could have just used this If(Question = 'Product', Answer) and it would have looked just like the expression above, but making a selection would have selected Question as well. To avoid selection in Question field... we used Aggr() with Only used so that I can use set analysis. If no condition was required then Aggr(Answer, Answer) could have worked as well.... but then why not just use Answer if there is no condition involved.

Table Measure:

Only({<Answer, CompanyName = p(CompanyName)>}Answer)

  • Only () = Only show these answers
  • '<Answer...' we're evaluating the answer (and company name)
  • 'CompanyName = p(CompanyName)' show all the company records where these companies are possible.

Set analysis is saying, ignore selection in Answer field, but only show the possible CompanyName/s

nayanmistry
New Contributor II

Re: Only show all company answer if a specific answer has been selected.

Hi Sunny,

That does make more sense to me now!

Thank you for your explanations and for your assistance with all this

Be well!

Nayan

Community Browser