Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
JohnSamuel123
Creator
Creator

set analysis combined count of two different values

hi,

i have a qlik sense expression:

count({<[RequestList]={'CBP', 'New'}>} [ID])

However, my bar chart counts each values for 'CBP 'and 'new'. i want an expression that will combine these two counts together rather than separate.

note: in the field 'Request List', i have 4 total values. i want the records that have 'CBP', 'New' counted together and the values 'Attr' 'End' counted together, so they can be displayed on a stacked bar chart with only two two measures instead of four.

is this possible? 

Labels (4)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

A better alternative than using a calculated dimension would to create a seperate table in the data model

Fact:

Load ID,RequestList,Area

From XXX:

 

dim_Grouping:

Load Distinct RequestList

,if(WildMatch(RequestList,'New','CBP'),'New & CBP','Attr & End') as RequestGroup

Resident Fact

;

 

then use RequestGroup in the bar chart

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

4 Replies
vinieme12
Champion III
Champion III

Not sure what you mean by  "these two counts together rather than separate."  

can you post some sample data and expected output?

This expression , count({<[RequestList]={'CBP', 'New'}>} [ID])  will count all ID's where RequestList is either CBP OR NEW 

Can the same ID have more than one  RequestList  ??

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
JohnSamuel123
Creator
Creator
Author

hi @vinieme12  here is the sample dataset. each row has a unique ID. however, i just want my bar chart to have 2 bars, one for "CBP and New" and another for "Attri and End". The Area will be my stack. 

ID Request List Area  
100 CBP Outside  
1001 New Outside  
102 Attr Inside  
103 End Inside  
104 New Outside  
105 End Outside  
vinieme12
Champion III
Champion III

Measure

=Count(ID)

Dimension

=aggr(if(WildMatch(RequestList,'New','CBP'),'New & CBP','Attr & End'),RequestList)

Bar

= Area

 

qlikCommunity1.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

A better alternative than using a calculated dimension would to create a seperate table in the data model

Fact:

Load ID,RequestList,Area

From XXX:

 

dim_Grouping:

Load Distinct RequestList

,if(WildMatch(RequestList,'New','CBP'),'New & CBP','Attr & End') as RequestGroup

Resident Fact

;

 

then use RequestGroup in the bar chart

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.