Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using Qlik Sense Desktop.
Below is the table and data I am using to illustrate.
Each person with Unique ID have 2 choices.
Only 1 choice will be offered. And each person has to indicate whether to accept the choice allocated to him/her.
What is the expression to count the number of person who have accepted 'Butter, as their choice? (ie Acceptance = 'Yes' and ((1st Choice = 'Butter' and ChoiceOffered = '1st') or (2ndChoice = 'Butter' and ChoiceOffered = '2nd'))
Thanks.
Unique ID | 1stChoice | 2ndChoice | ChoiceOffered | Acceptance |
1 | Bread | Butter | 2nd | No |
2 | Butter | Apple | 1st | Yes |
3 | Orange | Bread | 1st | Yes |
4 | Apple | Orange | 2nd | Yes |
5 | Bread | Butter | 2nd | No |
6 | Butter | Apple | 1st | Yes |
7 | Orange | Bread | 1st | Yes |
8 | Apple | Orange | 2nd | Yes |
9 | Bread | Butter | 2nd | No |
10 | Butter | Apple | 1st | Yes |
11 | Orange | Bread | 1st | Yes |
12 | Apple | Orange | 2nd | Yes |
13 | Bread | Butter | 2nd | No |
14 | Butter | Apple | 1st | Yes |
15 | Orange | Bread | 1st | Yes |
16 | Apple | Orange | 2nd | Yes |
17 | Bread | Butter | 2nd | No |
18 | Butter | Apple | 1st | Yes |
19 | Orange | Bread | 1st | Yes |
20 | Apple | Orange | 2nd | Yes |
Which field are you making the choice in? 1stChoice or 2ndChoice or there is another field where you make this selection?
I select 1st Choice chart but wanted to show KPI for both 1st Choice and 2nd Choice. What should be the expression?
I tried this below but I wanted to show KPI for say butter (be it butter being 1st or 2nd choice)
Count({<ChoiceOffered={'1st'},[Acceptance]= {'Yes'}>}Distinct [NRIC])+
Count({<ChoiceOffered={'2nd'},[Acceptance]= {'Yes'}>}Distinct [NRIC])
May be this
=Count({<ChoiceOffered = {'1st'}, Acceptance = {'Yes'}>+
<[2ndChoice] = p([1stChoice]), ChoiceOffered = {'2nd'}, Acceptance = {'Yes'}, [1stChoice]>} DISTINCT [Unique ID])
Thanks, Sunny. Will need to also count those 2nd choice butter who accepted.
The expression is already doing that