Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
raymondwee
Contributor III
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
niclaz79
Partner
Partner

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

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

jubarros
Luminary Alumni
Luminary Alumni

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

jneppl
Partner
Partner

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

+

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

ChennaiahNallani
Creator III
Creator III

try like below.

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

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

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

heena_shaikh
Contributor II
Contributor II

 
YoussefBelloum
Champion
Champion

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

sunny_talwar

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