How to get a count of stats list that contains a specific data?

[limalbert]

Hi all,

How to get a count of stats list that contains a specific data? Data is populated using stats and list() command.
Boundary: date and user. There are at least 1000 data. Sample example below.

Date User list(data)
3/31/17 user1 1, 2, 4

3/31/17 user2 1, 3

3/31/17 user3 8

Let say I want to count user who have list(data) that contains number bigger than "1". Then, the user count answer should be "3".
I tried using "| where 'list(data)' >1 | chart count(user) by date" , but it gives me a userCount of "1" for this case, as it ignores the list that have 3 or 2 data.

[woodcock]

It REALLY helps to have the whole search.

[somesoni2 · Accepted solution]

Try like this

your current search giving Date User list(data)
| where isnotnull(mvfilter('list(data)'>1))
| chart count(user) by date

[DalJeanis]

That one works by ignoring all values of the multivalue field list(data) that do not match your search criteria, in this case, >1. Those values are retained in the data, which is useful if you want to, for example, see what other values are present in records that have a particular value.

There's a less efficient method available as well, but a method that might seem more approachable to some beginners, and which would eliminate all the values that did not match. This method uses mvexpand to break up the single record with multiple values in the mv field into one record per mv value in the field. Remember, though, that if more than one of the multiple values might survive the filter, then you would be counting the number of VALUES, not the number of Users, so you use dc (distinct count) rather than count.

your current search giving Date User list(data)
| rename list(data) as mvdata
| mvexpand mvdata
| where (mvdata>1)
| chart dc(User) by Date