set analysis combined count of two different values

[JohnSamuel123]

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? 

[vinieme12]

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  ??

[JohnSamuel123]

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]

Measure

=Count(ID)

Dimension

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

Bar

= Area

[vinieme12 · Accepted solution]

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