New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New 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

Re: What is the expression to solve this?

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

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

Luminary

Re: What is the expression to solve this?

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

Esteemed Contributor

Re: What is the expression to solve this?

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

Re: What is the expression to solve this?

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

+

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

Highlighted
Contributor III

Re: What is the expression to solve this?

try like below.

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

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

New Contributor III

Re: What is the expression to solve this?

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.

New Contributor III

Re: What is the expression to solve this?

Hi,,

solution seems to be quite close.

I checked the output count is 5.

actual data consist of 8 such records.

regards,

Heena

Esteemed Contributor

Re: What is the expression to solve this?

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

MVP

Re: What is the expression to solve this?

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