How can I subtract timecharts from each other so I can get the total apples picked that day?

[ichesla1111]

Hello!

I am making a time chart for how many apples have been picked EACH day. Yet, the data field representing the number of picked apples is a cumulative sum over the month.
ex. Yesterday 5 apples were picked, today 3...instead of today's pick count=3 it is represented as 8 (5+3).

Given this, how can I make the time chart values subtract the number of apples previously picked from the current number of apples picked so I can get the number of apples picked that day.

Code:

Index...... |bin span=1d _time |dedup _time Apple_type
|stats sum(pick_count) as Picked by _time Apple_type
|timechart values(Picked) by Apple_type span=1d |fillnull value=0

Results:

 But I want!!!

Please help ! Thank you.

[johnhuang · Accepted solution]

Not sure why you're grouping by apple type when you want total apples picked that day.

<base_search>
| bucket _time span=1d
| stats max(pick_count) AS pick_ct BY _time
| sort 0 _time
| eval event_month=strftime(_time, "%Y-%m")
| streamstats current=f global=f max(pick_ct) AS last_ct BY event_month
| fillnull value=0 last_ct
| eval apples_picked=IF(pick_ct>last_ct, pick_ct-last_ct, 0)
| timechart span=1d max(apples_picked) AS apples_picked

[ichesla1111]

Thank you for getting back to me!!! I need to sort by apple type (Type) for we want to see the number of apples picked each day (pick_ct) so we can prioritize if we need more people to pick all the apples on time. 
Using your code I got the results...

Given this I changed the code and got....

|bucket _time span=1d
|stats max(pick_count) as pick_ct by _time Type
|sort 0 _time
|eval event_day=strftime(_time, "%Y-%m-%d")                               [want to count by day now]
|streamstats sum(pick_ct) as last_ct by event_day |dedup pick_ct
|eventstats sum(last_ct) as Done
|eval Done=if(pick_ct=last_ct,Done-last_ct,Done)
|eventstats min(pick_ct) as T
|eval Done=if(pick_ct<Done, Done-T, T)

Results:

I got the number from the previous day (which I want) BUT is there a way to reverse only the Done column OR am I overthinking, and it will not work. If I reverse it, I can do...
Done2=if(Done>pick_ct,Done-pick_ct,0)

[johnhuang]

This was what I used for testing. The reason to group by month when using streamstats is to ignore the last result of the previous month.

| makeresults
| eval i="2022-09-01,0;2022-09-02,0;2022-09-03,12;2022-09-04,0;2022-09-05,55;2022-09-06,83;2022-09-07,111;2022-09-08,0;2022-09-09,0;2022-09-10,0;2022-09-11,167;2022-09-12,227;2022-09-13,0;2022-09-14,0;2022-09-15,0;2022-10-01,0;2022-10-02,0;2022-10-03,12;2022-10-04,0;2022-10-05,55;2022-10-06,83;2022-10-07,111;2022-10-08,0;2022-10-09,0;2022-10-10,0;2022-10-11,167;2022-10-12,227;2022-10-13,0;2022-10-14,0;2022-10-15,0"
| eval i=split(i, ";")
| mvexpand i
| rex field=i "^(?<event_date>[^\,]*)\,(?<done_ct>.*)"
| eval _time=strptime(event_date, "%Y-%m-%d")
| eval event_month=strftime(_time, "%Y-%m")
| table _time event_month event_date done_ct
| sort 0 _time
```sum(eval(if(ItemsPurchased<0,0,ItemsPurchased)*UnitPrice)) ```
| streamstats current=f global=f max(done_ct) AS last_ct BY event_month
| fillnull value=0 last_ct
| eval apples_picked=IF(done_ct>last_ct, dont_ct-last_ct, 0)

[ichesla1111]

Thank you for helping me!! I really appreciate it, and I am learning from you. I'm so close!

What does the make results with eval "i" do? I can't use the make results command because it has to be at the top of my search, and I need to include my search results to get the data.

Code: 
my search...

| bucket _time span=1d
| stats max(pick_count) AS pick_ct by _time apple_type
| sort 0 _time
| eval event_month=strftime(_time, "%Y-%m")
| eval event_day=strftime(_time, "%Y-%m-%d")
| streamstats current=f global=f max(pick_ct) AS last_ct BY event_month apple_type
| fillnull value=0 last_ct
| eval apples_picked=IF(pick_ct>last_ct, pick_ct-last_ct, 0)
| timechart limit=15 span=1d max(apples_picked) AS apples_picked by apple_type


Results (past 7 days)

Issue 1: the last date is not accurate (boxed in red).
How I try to fix it: I tried to do min_time=info_min_time ...I did this so if _time=min_time then apples_picked=0 to remove this but then it turned every value to apples_picked=0

Issue 2 (using same search above): If I change the search time to month-date I get all zeros...why??

[johnhuang]

It will be helpful if you can provide a sample of the actual data, e.g. run this search and provide results:

<base_search> earliest=-1mon@mon
| fields _time Apple_type pick_count
| fillnull value=""
| eval i=epoch_time.",".Apple_type.",".pick_count
| stats list(i) AS i 
| eval data="\"".mvjoin(i, "|")."\""
| table data

[johnhuang]

The makeresults creates dummy data for the test instead of using your data -- try running entire query I posted.

[ichesla1111]

When I copy and pasted the code I got this

but the most recent search I did was close

[ichesla1111]

I did it!!! Thank you for helping me

[ichesla1111]

Code I used to get my answer

base search.....
| bucket _time span=1d
| stats max(Pick_count) AS pick_ct BY _time Apple_Type
| sort 0 _time
| eval event_month=strftime(_time, "%Y-%m")
| eval event_day=strftime(_time, "%Y-%m-%d")
| streamstats current=f global=f max(pick_ct) AS last_ct BY event_month Apple_Type
| fillnull value=0 last_ct
| eval apples_picked=IF(pick_ct>last_ct, pick_ct-last_ct, 0)
| eval timmme=strftime(_time, "%d") |streamstats min(timmme) as tt
|where timmme!=tt
| timechart limit=15 usenull=f useother=f cont=false span=1d max(apples_picked) AS apples_picked by Apple_Type