Qlik Community

New to Qlik Sense

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

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

count based on possible values

 

Hi - I have table as below.

IDType
1Active
1Inactive
2Active
2Pending

 

I have to get count of ID on each type -Active, Inactive, Pending but If ID has both Active & Inactive I only want to include the count in Active group.

Based on the data, below would be the counts, I want to show it in the KPI.

Active - 2

Inactive - 0

pending - 1

Thank you much.

Labels (6)
2 Solutions

Accepted Solutions
sunny_talwar

May be try this also

Sum(Aggr(If(Count(DISTINCT TOTAL <ID> {<Type = {'Active', 'Inactive'}>} Type) = 2 and Type = 'Inactive', 0, 1), ID, Type))

With Type as your dimension. 

View solution in original post

sunny_talwar

#Active - Count(DISTINCT {<Type *= {'Active'}>} ID)

#InActive - Count(DISTINCT {<Type *= {'Inactive'}, ID *= e({<Type={'Active'}>})>} ID)

#Pending - Count(DISTINCT {<Type *= {'Pending'}>} ID)

View solution in original post

5 Replies
tresesco
MVP
MVP

Inactive :

 =Count(DISTINCT {<Type={'Inactive'},ID=e({<Type={'Active'}>})>}ID)

sunny_talwar

May be try this also

Sum(Aggr(If(Count(DISTINCT TOTAL <ID> {<Type = {'Active', 'Inactive'}>} Type) = 2 and Type = 'Inactive', 0, 1), ID, Type))

With Type as your dimension. 

View solution in original post

apthansh
Creator
Creator
Author

@sunny_talwar Thank you - This gave me a total count which is correct.I want to put this in the KPI for each type.

#Active - 

#InActive - 

#Pending - 

 

Thank you.

sunny_talwar

#Active - Count(DISTINCT {<Type *= {'Active'}>} ID)

#InActive - Count(DISTINCT {<Type *= {'Inactive'}, ID *= e({<Type={'Active'}>})>} ID)

#Pending - Count(DISTINCT {<Type *= {'Pending'}>} ID)

View solution in original post

apthansh
Creator
Creator
Author

you are awesome.Thank you.