How can I compare count "so far" today with same range yesterday?

[lyndac]

I'm trying to use a single value with the trendline indicator to display this query:
|tstats count as count where index=foo ds=bar by _time span=1d | timechart sum(count) as count

It seems to work but I'm getting false trend downward trend indicators because if the query is run early in the day (say 9am), I may only have gotten 1000 events so far. But the trend indicator compares to the total number of events from yesterday (which could be as high as 50,000). So at 9am, the trend indicator is down by a huge percentage. As the day goes on and more events come in, the percentage gets smaller and then at the end of the day, you can finally see the true trend.

Is there a way to be able to compare the total for the time the query is run (9am) against the total yesterday up to the same time? That would give a way more reasonable trend.

[koshyk]

To compare two values between two time periods # Just change the earliest,latest and _time multipler for other dates

|tstats count where index=foo earliest=-2d@d latest=-1d@d by _time span=5m
| eval ReportKey="Baseline" | eval _time=_time+60*60*24
| append [|tstats count where index=foo earliest=-1d@d latest=@d by _time span=5m
| eval ReportKey="NewValue" ]
| timechart span=5m sum(count) by ReportKey

[woodcock]

To compare sum of (partial) today to the same partial sum of yesterday, do this (change 5m to suit your granularity needs):

| tstats count where index=foo ds=bar BY _time span=5m
| eval midnight=relative_time(now(), "@d"),
      yesternow=relative_time(now(), "-24h@m")
| where _time<yesternow OR _time>midnight
| timechart span=1d sum(count) as count

[cpetterborg]

Have you thought about using a summary table that gets populated every hour? Then you can compare the average over the last week, or some other time period. The reason that I ask about this is that you can compare more accurately if you have variations in the data coming in day-to-day. For example, if you have less data coming in on Sunday than you do on Monday, the data might not be very helpful to compare Monday to Sunday, but it might be more informative to to compare just to last Monday, or perhaps Monday to the average daily amount last week. tstats is nice to get data quickly, but if you do a summary index you have more variety in the comparison, and the summary search would be at least as fast. Especially because you could end up getting a single, quickly accessed event from your search.

[somesoni2]

Give this a try (accuracy level minute, assuming you always compare today with yesterday, may not work for other time ranges)

|tstats count as count where index=foo ds=bar by _time span=1m | where _time<=relative_time(now(),"24h@m) OR _time>=relative_time(now,"@d") | timechart span=1d sum(count) as count