I am trying to compute distinct counts of a field based on multiple conditions. Can anyone please help with the calc that would help to compute distinct counts?
Example:
ID Index Index(2)
1 CA A
1 NY A
1 NY A
2 CA B
2 CA B
3 CA A
3 NY A
3 NY A
I am trying to get the distinct count of ID by Index(2) where Index(1) = "CA". Any help will be highly appreciated. thanks.
Give this a try
your current search giving fields ID, Index, and Index(2)
| eval CA_ID=if(Index="CA",ID,null())
| stats count(ID) as count dc(ID) as IDs dc(CA_ID) as CA_IDs by "Indexe(2)"
Give this a try
your current search giving fields ID, Index, and Index(2)
| eval CA_ID=if(Index="CA",ID,null())
| stats count(ID) as count dc(ID) as IDs dc(CA_ID) as CA_IDs by "Indexe(2)"
Awesome! Many thanks. This worked perfectly... I was using the same but in if condition I was not ID for true value and hence was not getting correct results.
thanks for the help
Can you try this please:
<yourbasesearch> | stats count(ID) dc(ID) dc(eval(!isnull(ID) AND index="CA")) as val3 by Index(2)
@bhumikajpatel, are you test my proposition?
Hi, Yes I did.. and I had tried this approach earlier as well... It gives my count as 1 for instead I needed the distinct count of Ids. Thanks for the help.
I think this will fit your needs:
your current base search
| search Index(1)="CA"
| stats dc(ID) AS ID_count BY Index(1)
You could also push that Index(1)="CA"
into the base search, presumably.
Apologies, I was not clear in my original post...
I need two types of counts.
COUNT(ID), DISTINCT_COUNT(ID) by INDEX(2), and DISTINCT_COUNT(ID) by INDEX(2)but with Index ="CA" as the condition.
Can we have such combined counts? thanks for the help.