Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

raymondwee
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 ID1stChoice2ndChoiceChoiceOfferedAcceptance
1BreadButter2ndNo
2ButterApple1stYes
3OrangeBread1stYes
4AppleOrange2ndYes
5BreadButter2ndNo
6ButterApple1stYes
7OrangeBread1stYes
8AppleOrange2ndYes
9BreadButter2ndNo
10ButterApple1stYes
11OrangeBread1stYes
12AppleOrange2ndYes
13BreadButter2ndNo
14ButterApple1stYes
15OrangeBread1stYes
16AppleOrange2ndYes
17BreadButter2ndNo
18ButterApple1stYes
19OrangeBread1stYes
20AppleOrange2ndYes
14 Replies
Partner
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
Luminary

Re: What is the expression to solve this?

Hi WEE Keat Kheng Raymond:

Try to calculate a new field on script like:

LOAD

     *,

     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

YoussefBelloum
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
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
ChennaiahNallani
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))

raymondwee
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.

heena_shaikh
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.

please help if possible

regards,

Heena

YoussefBelloum
Esteemed Contributor

Re: What is the expression to solve this?

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

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