topic Re: Average Per day of Month in QlikView

Average Per day of Month

kroberts13 — Tue, 31 Oct 2017 11:20:12 GMT

Hello,

I am looking for a way to get an average amount per day of the month over the last 12 months. For example, I would need to sum the amount on 12/1, 1/1, 2/1, etc. and average that number. I would need to do that for every day. Any help on this would be greatly appreciated.

Thanks in advance!

Re: Average Per day of Month

sunny_talwar — Tue, 31 Oct 2017 11:23:47 GMT

May be this

Sum(Aggr(Avg(Value), Date))

Re: Average Per day of Month

jonathandienst — Tue, 31 Oct 2017 11:31:02 GMT

Sunny, don't you mean:

Avg(Aggr(Sum(Value), Date))

Re: Average Per day of Month

sunny_talwar — Tue, 31 Oct 2017 11:34:42 GMT

Not sure I am clear if OP wants to do Avg each day and then sum or sum each day and then average.... but you could be right

Re: Average Per day of Month

kroberts13 — Tue, 31 Oct 2017 11:36:41 GMT

I would want to sum every day (12/1, 1/1, 2/1) and then average

Re: Average Per day of Month

sunny_talwar — Tue, 31 Oct 2017 11:38:07 GMT

Then jontydkpi‌'s response is the right one I guess

Re: Average Per day of Month

effinty2112 — Tue, 31 Oct 2017 11:38:08 GMT

Hi Kyle,

try a straight table with calculated dimension

=Day(Date)

and expression

avg(Aggr(Sum(Amount),Date))

Good luck

Andrew

Re: Average Per day of Month

prat1507 — Tue, 31 Oct 2017 11:49:40 GMT

Maybe this

=avg(Aggr(Sum(Amount),YearField,MonthField))

Regards

Pratyush

Re: Average Per day of Month

kroberts13 — Tue, 31 Oct 2017 13:01:46 GMT

How would I just grab the last 12 months?

Re: Average Per day of Month

sunny_talwar — Tue, 31 Oct 2017 13:03:06 GMT

Are you trying to get rolling 12 months?

Re: Average Per day of Month

kroberts13 — Tue, 31 Oct 2017 13:06:28 GMT

Yes, Rolling 12 months. I am using the set Analysis below:

RangeSum(above(Aggr(Sum({$<[DETAIL_TYPE] = {1,10}>}AMOUNT), POST_DATE),0,rowno()))

But it is giving me current month numbers, rather than averaging the last 12 months per day.

Re: Average Per day of Month

sunny_talwar — Tue, 31 Oct 2017 13:08:05 GMT

I would suggest to use The As-Of Table to resolve this.... it will be efficient and easy to maintain

Re: Average Per day of Month

kroberts13 — Tue, 31 Oct 2017 14:35:09 GMT

This would be the best way to look at the prior 12 months?

Re: Average Per day of Month

sunny_talwar — Tue, 31 Oct 2017 14:38:32 GMT

I would say the most efficient way to do it... other methods are going to be somewhat complex and will slow down the chart