# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for
Did you mean:
Contributor III

## What is the expression to solve this?

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
14 Replies
Partner

Count({<1stChoice = {'Butter'}, Acceptance = {'Yes'}>}Distinct [Unique ID]) +

Count({<2ndChoice = {'Butter'}, ChoiceOffered = {'2nd'}>}Distinct [Unique ID])

Luminary Alumni

Hi WEE Keat Kheng Raymond:

Try to calculate a new field on script like:

*,

if(Acceptance='Yes', if(ChoiceOffered='1 st', 1stChoice, 2ndChoice)) as ChoiceAcceptance

...

after this you only need:

count({<ChoiceAcceptance={'Butter'}>} ID)

Best Regards,

Juan P. Barroso

Champion

Hi,

try this as an expression with UniqueId as a dimension and check if you get the expected output:

=count(if(Acceptance='Yes' and (([1stChoice]='Butter' and ChoiceOffered='1st') or (ChoiceOffered='2nd' and [2ndChoice]='Butter')),1))

Partner

Count({<[1stChoice] = {'Butter'}, Acceptance = {'Yes'}, ChoiceOffered = {'1st'}>} Distinct [Unique ID])

+

Count({<[2ndChoice] = {'Butter'}, Acceptance = {'Yes'}, ChoiceOffered = {'2nd'}>} Distinct [Unique ID])

Creator III

try like below.

count(if(Acceptance = 'Yes' and ([1stChoice]='Butter' and ChoiceOffered='1st') or

(([2ndChoice]='Butter' and ChoiceOffered='2nd')),UniqueID))

Contributor III
Author

Thanks for all the responses.

Sorry, missed out these information:

I have created a bar chart for counting butter, apple, orange and bread items for 1stChoice and another bar chart for AllChoices with those items.

And I am creating a "KPI" diagram on top of this bar chart, in Qlik Sense Desktop.

I would like to track count for this "KPI". Whenever an item ( butter, apple, orange and bread) is selected in the bar charts, this KPI will show the number of person who have accepted an item (eg 'Butter'), as their choice.

What is the expression for this KPI?

Thanks.

Contributor II

Champion

using the above table, what is the expected output (lines) ?

MVP

Another option is to combine them into a single expression

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

Tags
Community Browser