I have this search (executed over last 7 days):
sourcetype=access* action=purchase | bucket _time span=1d | convert ctime(time) timeformat="%m/%d" | chart sum(price) as sales over productId by _time
that is returning me a table having dates as columns name and productId as rows.
I can't find out how to caluclate the difference between columns of the report (for example, the first column, that is the first day, and the last column=last day).
Thanks in advance and best regards.
I think you want autoregress and stats command.
#Should be shorted by productId then by time. Becarefull of short order. This should give you the delta.
sourcetype=access_* action=purchase | bucket _time span=1d | convert ctime(_time) timeformat="%m/%d" | stats sum(price) as sales by productId ,_time |autoregress productId as preproductId | autoregress sales as presales | eval chg_in_sales=(sales-presales)| eval chg_in_sales=if(productId==preproductId,chg_in_sales,null()| table _time,productId,sales,chg_in_sales
If you want to a percentage of total sales by productId I might consider doing a subsearch with join and filldown. I am also doing this off the cuff so you may need a fields and sort command before the first autoregress.
Check out my other post.
Hope this helps are gets you started. Dont forget to vote up and/or accept answers.
I have solved my issue in this way:
1) I populate a summary index every hour with this search:
ourcetype=access_* action=purchase earliest=-7d@d latest=@d | bucket _time span=1d | stats sum(price) by _time productId
2) I have created a search with many join commands to get final results...
Thank you bmacias84.
but I think your solution cannot be applied.
I need to keep dates as column names (example: "3/21", "3/22", "3/23") and I want to add a final column that is the difference in percentage between the first and last column (in the example the differences between values under "3/21" and "3/23").