Use aggr to calculate seven day average

[#1]

Hello,

I need to calculate a last seven-day average by selection of date.

I used the following calculation but doesn't seem to give me the desired result.It only work if I make no selection (in this case will calculate last 7-day average from max(event_date). Or I have select all seven days in order for it to render the correct answer. Please help!

avg(aggr(sum({$<event_date={">$(=date(max(event_date)-7,'YYYY-MM-DD'))<=$(=max(event_date))"}>}usercnt),event_date))

Xinzhen

[brunobertels]

Hi

may be this

avg(aggr(sum({$<event_date={">$(=date(max(event_date)-7,'YYYY-MM-DD'))<=$(=date(max(event_date),'YYYY-MM-DD')"}>}usercnt),event_date))

[sunny_talwar · Accepted solution]

Try giving the set analysis to your outer aggregation function also

Avg({$<event_date={">$(=date(max(event_date)-7,'YYYY-MM-DD'))<=$(=max(event_date))"}>} Aggr(Sum({$<event_date={">$(=date(max(event_date)-7,'YYYY-MM-DD'))<=$(=max(event_date))"}>} usercnt), event_date))

[#4]

Thank you Sunny!!

This worked! What is the reason I need set analysis in both inside and outside of aggr?

Xinzhen

[sunny_talwar]

Since your outer aggregation was not set to show any date range, it was drilling down to the current selection even though your inner aggregation was set. Its always advisable to set the limits used in inner set analysis in your outer aggregation function also unless you are deliberately looking to let it change based on current selection.