Timelion - Find unique count on multiple conditions

[thomasdang (TD)]

Hi Experts,

I need to calculate the unique number of string values in a field with multiple query conditions. Here's an example:

There are 2 fields:
CUSTOMER_NAME (with values like "John", "Jack", "Marie", etc.)
BOUGHT_ITEM (with values like 1, 2, 3, 4, etc. - these are string values, not numbers)

Example table:
CUSTOMER_NAME, BOUGHT_ITEM
John, 1
Jack, 2
Marie, 1
John, 2
Jack, 3
Marie, 2
Bob, 2
Jack, 4

I need to run a Timelion command to select unique count of CUSTOMER_NAME who bought (at least) both items 1 and 2 (at different times). Therefore, the result would be 2 (customers) because John and Marie bought items 1 and 2.

The following Timelion query ONLY counts unique customer who bought item 1. How do I make it so to get unique customer count who bought both items 1 AND 2?

.es(index=customer, q='BOUGHT_ITEM:1', metric=cardinality:CUSTOMER_NAME.keyword)

Please help! Thanks so much!

[alisongoryachev]

Hi @thomasdang!

Timelion uses query-string syntax, so I believe you should be able to use AND in the query. Something like: .es(index=customer, q='BOUGHT_ITEM:(1 AND 2)', metric=cardinality:CUSTOMER_NAME.keyword)

[thomasdang (TD)]

Thanks @alisongoryachev. When I tried that, it gave me no result! But when I tried ... (1 OR 2) then it works, but of course that's not what I need. I think the "AND" doesn't work because it thinks the field (BOUGHT_ITEM) cannot be both 1 and 2 at the same time! But what we need here is to find the same CUSTOMER_NAME that bought items 1 and 2 perhaps at different time. I guess what we're looking for is the "union" of items 1 and 2. So what I had to do manually is to query customers who bought item 1, and do another query for customers who bought item 2, then union those 2 sets and find the common CUSTOMER_NAME who bought both items 1 & 2. But that's a long way, I need to do all that in the same query. Thanks!