Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I have table as below.
ID | Type |
1 | Active |
1 | Inactive |
2 | Active |
2 | Pending |
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.
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.
#Active - Count(DISTINCT {<Type *= {'Active'}>} ID)
#InActive - Count(DISTINCT {<Type *= {'Inactive'}, ID *= e({<Type={'Active'}>})>} ID)
#Pending - Count(DISTINCT {<Type *= {'Pending'}>} ID)
Inactive :
=Count(DISTINCT {<Type={'Inactive'},ID=e({<Type={'Active'}>})>}ID)
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.
@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.
#Active - Count(DISTINCT {<Type *= {'Active'}>} ID)
#InActive - Count(DISTINCT {<Type *= {'Inactive'}, ID *= e({<Type={'Active'}>})>} ID)
#Pending - Count(DISTINCT {<Type *= {'Pending'}>} ID)
you are awesome.Thank you.