The SPL below returns a count from one field in multiple CSVs. At the end, a delta is calculated, comparing each count to the count before it. That difference (or delta) is then displayed as a line chart overlay:
|inputlookup inventory20180815.csv
|stats count(computer_id) as 20180815
|appendcols [|inputlookup inventory20180822.csv | stats count(computer_id) as 20180822]
|appendcols [|inputlookup inventory20180830.csv | stats count(computer_id) as 20180830]
|appendcols [|inputlookup inventory20180904.csv | stats count(computer_id) as 20180904]
|appendcols [|inputlookup inventory20180910.csv | stats count(computer_id) as 20180910]
|appendcols [|inputlookup inventory20180917.csv | stats count(computer_id) as 20180917]
|appendcols [|inputlookup inventory20180924.csv | stats count(computer_id) as 20180924]
|appendcols [|inputlookup inventory20181001.csv | stats count(computer_id) as 20181001]
|appendcols [|inputlookup inventory20181015.csv | stats count(computer_id) as 20181015]
|transpose
|delta "row 1" as "Change over Time"
|rename "row 1" as Dates column as "Weekly Computer Count"
Statistics Table:
Column Chart w/ Chart Overlay:
My question is how do I populate a new field so that it takes each count and compares it to the very first count ( |stats count(computer_id) as 20180815
)? For example, if I had |stats counts that returned 5, 10, and 20, the new field should return 0, 5, 15 because it calculates 5-5, 10-5, and 20-5. My intent is to use this new field and turn it into a line chart overlay (thus replacing the line chart overlay I currently have).
Give this a try
|inputlookup inventory20180815.csv
|stats count(computer_id) as 20180815
|appendcols [|inputlookup inventory20180822.csv | stats count(computer_id) as 20180822]
|appendcols [|inputlookup inventory20180830.csv | stats count(computer_id) as 20180830]
|appendcols [|inputlookup inventory20180904.csv | stats count(computer_id) as 20180904]
|appendcols [|inputlookup inventory20180910.csv | stats count(computer_id) as 20180910]
|appendcols [|inputlookup inventory20180917.csv | stats count(computer_id) as 20180917]
|appendcols [|inputlookup inventory20180924.csv | stats count(computer_id) as 20180924]
|appendcols [|inputlookup inventory20181001.csv | stats count(computer_id) as 20181001]
|appendcols [|inputlookup inventory20181015.csv | stats count(computer_id) as 20181015]
|transpose
|rename "row 1" as Dates column as "Weekly Computer Count"
| eventstats first(Dates) as first
| eval "Change over time"=Dates-first | fields - first
Give this a try
|inputlookup inventory20180815.csv
|stats count(computer_id) as 20180815
|appendcols [|inputlookup inventory20180822.csv | stats count(computer_id) as 20180822]
|appendcols [|inputlookup inventory20180830.csv | stats count(computer_id) as 20180830]
|appendcols [|inputlookup inventory20180904.csv | stats count(computer_id) as 20180904]
|appendcols [|inputlookup inventory20180910.csv | stats count(computer_id) as 20180910]
|appendcols [|inputlookup inventory20180917.csv | stats count(computer_id) as 20180917]
|appendcols [|inputlookup inventory20180924.csv | stats count(computer_id) as 20180924]
|appendcols [|inputlookup inventory20181001.csv | stats count(computer_id) as 20181001]
|appendcols [|inputlookup inventory20181015.csv | stats count(computer_id) as 20181015]
|transpose
|rename "row 1" as Dates column as "Weekly Computer Count"
| eventstats first(Dates) as first
| eval "Change over time"=Dates-first | fields - first
This does exactly what I asked to have done. Thank you!