topic Re: How to add unique count column on the stats result in Splunk Search

How to add unique count column on the stats result?

mia — Fri, 16 Jun 2023 15:50:50 GMT

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.

Re: How to add unique count column on the stats result

ITWhisperer — Fri, 16 Jun 2023 13:22:47 GMT

| 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

Re: How to add unique count column on the stats result

mia — Fri, 16 Jun 2023 15:34:37 GMT

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:

Re: How to add unique count column on the stats result

mia — Fri, 16 Jun 2023 15:45:29 GMT

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