Table with count and latest date

[giuces]

Hi all,

i need to create a table that count for every product how many events are accepted or rejected.

In addition to this fields the latest event date should be shown with the count. Table should be like this

Product	Latest Accepted	Total Accepted	Latest Rejected	Total Rejected
Bike	10/11/2021	35	12/11/2021	14
Skate	11/11/2021	99	13/11/2021	5

the first part of the query is pretty easy:

...| stats count(eval(action="accepted)) AS "Total Accepted" count(eval(action="rejected)) AS "Total rejected" by product | rename product AS Product

I'm not able to retrieve the latest date for every  kind of action, tried with latest(_time) without success.

Many thanks

[aasabatini]

Hi @giuces 

try the stats function like this:

stats latest(eval(if(action="rejected", _time, NULL()))) AS "Latest Rejected"

Ale 

[giuces]

thanks aasabatini,

i'm little be confused where do i have to insert portion of search yo suggest in the old one

...| stats count(eval(action="accepted)) AS "Total Accepted" count(eval(action="rejected)) AS "Total rejected" by product | rename product AS Product | table Product "Total Accepted" "Total Rejected" ?

Regards

[aasabatini]

Hi @giuces 

try like this

 stats count(eval(action="accepted)) AS "Total Accepted" count(eval(action="rejected)) AS "Total rejected" latest(eval(if(action="rejected", _time, NULL()))) AS "Latest Rejected" by product | rename product AS Product