How to calculate and display in a table the average of each user's field values for n number of days?

[pavanae]

I have a search which gives the result as follows for one day

Query :- base search | stats dc(dCIF) as dUniqueCIFs by user |eventstats avg(dUniqueCIFs) as avgdUniqueCIFs |stats max(dUniqueCIFs) as max_dUniqueCIFs max(avgdUniqueCIFs) as avgdUniqueCIFs by user

Users     dc(users_activity_count)     average(All_users_activity_count)
A                             2                                2              
B                             3                                2               
C                             1                                2                

Similarly how can I calculate the average of each users_activity count for n number of days like below

Users     dc(users_activity_count)     average(All_users_activity_count)   average(each_users_activity_count_forlast7days)
A                             2                               2                      A's average for last 7 days     
B                             3                               2                      B's .......
C                             1                               2                      C's.........

Where average(each_users_activity_count_forlast7days) should display the average of each user for last 7 days, which is user A's Average,B's,C's....

[sundareshr · Accepted solution]

Try something like this

base search earliest=-7d@d
| bin span=1d _time
| stats dc(dCIF) as dUniqueCIFs by _time user 
| eval when=if(_time<relative_time(now(), "@d"), "7days", "Today")
| eventstats avg(dUniqueCIFs) as avgdUniqueCIFs by when
| table user dUniqueCIFs 7days Today 
| dedup user

[pavanae]

didn't worked. My query is as follows

base search | stats dc(dCIF) as dUniqueCIFs by user |eventstats avg(dUniqueCIFs) as avgdUniqueCIFs |stats max(dUniqueCIFs) as max_dUniqueCIFs max(avgdUniqueCIFs) as avgdUniqueCIFs by user

Based on your suggestion. I'd modified and the result successfull displayed the dUniqueCIFs which I mentioned as dc(users_activity_count) in my question but didn't seen the average and 7days average. @sundareshr

[sundareshr]

Try the updated query

[pavanae]

still the same no results for 7days and today. @sundareshr