Delta in percentage between columns in a report

[cafissimo]

Hello,
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.

[bmacias84]

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.

line-chart-cumulative-counters-by-host

Hope this helps are gets you started. Dont forget to vote up and/or accept answers.

Cheers,

[cafissimo]

Hello,
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.

[gfuente]

Hello

Maybe you could use traspose command to switch rows and columns, and then use delta command to calculate the difference between each row.

Regards

[cafissimo]

Thank you,
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").
Kind regards.