Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Count({<1stChoice = {'Butter'}, Acceptance = {'Yes'}>}Distinct [Unique ID]) +
Count({<2ndChoice = {'Butter'}, ChoiceOffered = {'2nd'}>}Distinct [Unique ID])
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
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))
Count({<[1stChoice] = {'Butter'}, Acceptance = {'Yes'}, ChoiceOffered = {'1st'}>} Distinct [Unique ID])
+
Count({<[2ndChoice] = {'Butter'}, Acceptance = {'Yes'}, ChoiceOffered = {'2nd'}>} Distinct [Unique ID])
try like below.
count(if(Acceptance = 'Yes' and ([1stChoice]='Butter' and ChoiceOffered='1st') or
(([2ndChoice]='Butter' and ChoiceOffered='2nd')),UniqueID))
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.
using the above table, what is the expected output (lines) ?
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])