trend from last week/month/quarter

[sanketsahoo]

Hello Everyone,

I am stuck at building a trending dashboard.

My data in table format:-

_time,  ProjectName, summary1, summary2

2021-04-06 05:41:30.027	ProjectA	121	173
2021-04-07 07:06:00.983	ProjectA	121	173
2021-04-08 02:30:47.883	ProjectA	121	173
2021-04-09 05:09:43.243	ProjectA	130	173
2021-04-10 12:07:51.513	ProjectA	130	173

I want to build a dashboard visualization such as a comparison for summary data and yesterday data/last weeks data, last month , last quarter data based on a input field.

So that we can derive what was the summary last week for project A, last month for project A, and so on

I tried 
the search 
| timechart avg(summary1), avg(summary2) by ProjectName span=w@w1
| timewrap 1mon

[ericjorgensenjr · Accepted solution]

This can be accomplished in different ways, but I would recommend something like this:

<your search> earliest=-1w latest=now | eval ProjectName=ProjectName." - current" | append [ search <your search> earliest=-2w latest=-1w | eval ProjectName=ProjectName." - previous", _time=_time+604800 ] | timechart avg(summary1) as summary1, avg(summary2) as summary2 by ProjectName

It's possible to replace the earliest and latest from both searches with variables from your form inputs. For example, the user could use a time picker for the time in the main search, then you can have an input where the user enters the number of days/weeks/etc... to shift back. Then you eval the earliest time token, (e.g. <eval token="sub_earliest">$time_token.earliest$-$input_shift_token$*24*60*60</eval>, this should give you epoch time for earliest, replace in your subsearch earliest=$sub_earliest$, then follow the same process for latest).

Hope this helps!

[sanketsahoo]

 Thanks it helps.

[ITWhisperer]

What determines whether the data is from yesterday (presumably this is relative to today?) or last 24 hours?, what about last week (data from 7 days ago or last 7 days or last Sunday to previous Monday)? What about last month (same day of the month in the previous month - what about when the lengths aren't the same - last 30 days, the whole of last month)? Perhaps, you could try adding columns tagging each event with whether it should be included in the summary for yesterday, last week, last month, last quarter etc. Then you might be able to use eventstats to summarise your statistics over those periods.