4 Replies Latest reply: Nov 21, 2016 8:42 PM by Sunny Talwar

# Use aggr to calculate seven day average

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

• ###### Re: Use aggr to calculate seven day average

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))

• ###### Re: Use aggr to calculate seven day average

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))

• ###### Re: Use aggr to calculate seven day average

Thank you Sunny!!

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

Xinzhen

• ###### Re: Use aggr to calculate seven day average

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.