How to compare products sold today vs last week same day?

[Prathyusha891]

Today : index=sold Product=Acer , Product=iphone 
last week : index=sold  Product=Samsung , Product=iphone

Query Used :
index=sold earliest=-0d@d latest=now
|stats count as Today by Product
| appendcols [search index=sold earliest=-7d@d latest=-6d@d |stats count as Lastweeksameday by Product]


---
As Samsung Product is not sold on "Today" that particular Product is not showing up in the Output though it was sold on "Last week". Ideally it should show as 0 for "Today" and as 1 for "Last week" in the output. Could someone please help.

[Prathyusha891]

Hi @bowesmana  Thanks for the help.
But I see count is not matching for the last week same day using the below query though its totally working fine for today's count. Could you please help.

index=sold (earliest=-d@d latest=now) OR (earliest=-7d@d latest=-6d@d)
| eval Today=if(_time>=relative_time(now(), "@d"), 1, null())
| stats count(eval(if(isnull(Today), 1, null()))) as LastWeek count(eval(if(isnotnull(Today), 1, null()))) as Today by Product

[Prathyusha891]

Modified as  "(earliest=-0@d latest=now)" and able to get the solution. Thankyou.

[bowesmana · Accepted solution]

Avoid appendcols - it is rarely the solution to any problem.

A number of ways to do this

index=sold (earliest=-d@d latest=now) OR (earliest=-7d@d latest=-6d@d)
| bin _time span=1w
| stats count by _time Product

or

index=sold (earliest=-d@d latest=now) OR (earliest=-7d@d latest=-6d@d)
| timechart span=1w count by Product

These will give you two forms of output. Depending on how you want to visualise, you can manipulate the data. For example if you do the timechart variant, you can do

| eval Day=strftime(_time, "%F")
| fields - _time
| transpose 0 header_field=Day

To turn it into a Product/Date/Date format

You can also turn dates into "Today" and "Last week", but that will assume that Today is always "today".

Or you can do it this way

index=sold (earliest=-d@d latest=now) OR (earliest=-7d@d latest=-6d@d)
| eval Today=if(_time>=relative_time(now(), "@d"), 1, null())
| stats count(eval(if(isnull(Today), 1, null()))) as LastWeek count(eval(if(isnotnull(Today), 1, null()))) as Today by Product

which sets a flag depending on whether the event time is today or not and then performs different counts according to that flag.

Hope this helps.