topic Re: Compare products sold today vs last week same day in Splunk Search

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

Prathyusha891 — Thu, 09 Feb 2023 16:10:17 GMT

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.

Re: Compare products sold today vs last week same day

bowesmana — Tue, 07 Feb 2023 06:13:45 GMT

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

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.

Re: Compare products sold today vs last week same day

Prathyusha891 — Tue, 07 Feb 2023 06:40:30 GMT

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

Re: Compare products sold today vs last week same day

Prathyusha891 — Tue, 07 Feb 2023 07:59:24 GMT

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