How to add unique count column on the stats result?

[mia]

Hi, 

I have data as below 

| date | buyer | product |
| Jun-1 | A      | P-01 |
| Jun-1 | A      | P-02 |
| Jun-1 | B      | P-03 |
| Jun-1 | A      | P-03 |
| Jun-5 | A      | P-02 |
| Jun-5 | A      | P-01 |
| Jun-5 | A      | P-02 |
| Jun-5 | C      | P-02 |

| date  | P-01 | P-02 | P-03  | daily unique buyer |
|Jun-1 |   1     |    1       | 2        |          2 |
|Jun-5 |    1    |    2       | 0        |          2 |

I want to stats count daily unique buyer and daily uniquer buyer by product

I use  chart dc(buyer) as uuBuyer by date , product

but I don't know how to do the last column I expect count the daily unique buyer

Do anyone could give me a solution? 

thanks.

[ITWhisperer]

| eventstats dc(buyer) as uniquebuyer by date
| stats count by date uniquebuyer product
| eval {product}=count
| fields - product count
| stats values(*) as * values(uniquebuyer) as uniquebuyer by date
| fillnull value=0

[mia · Accepted solution]

Hi, @ITWhisperer 

Thanks for your reply.

I got inspire the form your command.

I modified and then the result is correct.

| eventstats dc(buyer) as uniquebuyer by date
| stats dc(buyer) AS uu by date product uniquebuyer
| eval {product}=uu
| fields -product uu
| stats values(*) as * values(uniquebuyer) as uniquebuyer by date
| fillnull value=0

[mia]

Hi, @ITWhisperer 

I try your command but the result count of  daily unique buyer by product is wrong.

I compare the result with calculated separately 2 command

• chart dc(buyer) as uniquebuyer by date, product -> your result not match, not unique buyer
• chart dc(buyer) as uniquebuyer by date. -> uniquebuyer is match

---

@ITWhisperer wrote:

| eventstats dc(buyer) as uniquebuyer by date
| stats count by date uniquebuyer product
| eval {product}=count
| fields - product count
| stats values(*) as * values(uniquebuyer) as uniquebuyer by date
| fillnull value=0

---