topic Use aggr to calculate seven day average in App Development

Use aggr to calculate seven day average

Sun, 20 Nov 2016 18:00:51 GMT

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

brunobertels — Mon, 21 Nov 2016 12:31:08 GMT

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

sunny_talwar — Mon, 21 Nov 2016 12:36:25 GMT

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

Mon, 21 Nov 2016 23:33:24 GMT

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

sunny_talwar — Tue, 22 Nov 2016 01:42:34 GMT

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.