Splunk Search

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

Prathyusha891
Explorer

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.

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

View solution in original post

Prathyusha891
Explorer

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

  

0 Karma

Prathyusha891
Explorer

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...

Observability Unlocked: Kubernetes & Cloud Monitoring with Splunk IM

Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team on ...