What is the expression to solve this?

[Anonymous]

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

[sunny_talwar]

Which field are you making the choice in? 1stChoice or 2ndChoice or there is another field where you make this selection?

[Anonymous]

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])

[sunny_talwar]

May be this

=Count({<ChoiceOffered = {'1st'}, Acceptance = {'Yes'}>+

<[2ndChoice] = p([1stChoice]), ChoiceOffered = {'2nd'}, Acceptance = {'Yes'}, [1stChoice]>} DISTINCT [Unique ID])

[Anonymous]

Thanks, Sunny. Will need to also count those 2nd choice butter who accepted.

[sunny_talwar]

The expression is already doing that