topic Re: Rolling Average per day of month in QlikView

Rolling Average per day of month

kroberts13 — Tue, 27 Dec 2016 15:25:20 GMT

Hello,

I am trying to get a rolling average for each day of the month.

I have a total budget number for each month. I am trying to Get the average per day and make that a rolling number up through the end of the month. Does anyone have any idea on how to do so? Thank you in advance for your help and please let me know if any further information is needed.

Thank you

Re: Rolling Average per day of month

sunny_talwar — Tue, 27 Dec 2016 15:30:17 GMT

May be look into: The As-Of Table

Re: Rolling Average per day of month

sunny_talwar — Tue, 27 Dec 2016 15:30:45 GMT

or may be this:

Calculating rolling n-period totals, averages or other aggregations

Re: Rolling Average per day of month

kroberts13 — Thu, 29 Dec 2016 18:49:09 GMT

Still no luck. It seems simple, I am just struggling with it. I have the monthly budget number and just need to average that out daily with a running sum

Re: Rolling Average per day of month

sunny_talwar — Thu, 29 Dec 2016 18:50:55 GMT

Would you be able to share a sample with expected output?

Re: Rolling Average per day of month

kroberts13 — Thu, 29 Dec 2016 18:56:00 GMT

I have the total ($96369197.32) for each month in the format of mm/YYYY. That is what I am trying to average per day with the running sum to eventually show that total on the last day of every month.

Re: Rolling Average per day of month

sunny_talwar — Thu, 29 Dec 2016 18:57:43 GMT

So you want to divide 96369197.32 equal among each of the day within a month?

Re: Rolling Average per day of month

kroberts13 — Thu, 29 Dec 2016 18:59:36 GMT

Essentially, yes

Re: Rolling Average per day of month

sunny_talwar — Thu, 29 Dec 2016 19:06:41 GMT

Something along these lines

Table:

LOAD *,

If(MonthYear = Previous(MonthYear), RangeSum(Peek('CumAmountPerDay'), AmountPerDay), AmountPerDay) as CumAmountPerDay;

LOAD *,

Date(MonthYear + IterNo() - 1) as Date

While MonthYear + IterNo() - 1 <= MonthEnd(MonthYear);

LOAD Date(MonthStart(Date#(MonthYear, 'MMM-YYYY')), 'MMM-YYYY') as MonthYear,

Amount,

Amount/Day(MonthEnd(Date#(MonthYear, 'MMM-YYYY'))) as AmountPerDay;

LOAD * Inline [

MonthYear, Amount

Nov-2016, 64523214.21

Dec-2016, 96369197.32

];

Re: Rolling Average per day of month

kroberts13 — Fri, 30 Dec 2016 15:36:44 GMT

Perfect! This worked! I have one more small question, do you mind emailing me? kyle.roberts@osumc.edu

Thank you so much for your help!

Re: Rolling Average per day of month

kroberts13 — Tue, 10 Jan 2017 14:39:54 GMT

Sunny,

Is it possible to Make the Date field above 'MM-DD-YYY'? While keeping the Month Year piece as it is?

Re: Rolling Average per day of month

kroberts13 — Tue, 10 Jan 2017 14:40:20 GMT

Sunny,

Is it possible to Make the Date field above 'MM-DD-YYY'? While keeping the Month Year piece as it is?

Re: Rolling Average per day of month

sunny_talwar — Tue, 10 Jan 2017 15:25:33 GMT

May be like this

Table:

LOAD *,

If(MonthYear = Previous(MonthYear), RangeSum(Peek('CumAmountPerDay'), AmountPerDay), AmountPerDay) as CumAmountPerDay;

LOAD *,

Date(MonthYear + IterNo() - 1, 'MM-DD-YYYY') as Date

While MonthYear + IterNo() - 1 <= MonthEnd(MonthYear);

LOAD Date(MonthStart(Date#(MonthYear, 'MMM-YYYY')), 'MMM-YYYY') as MonthYear,

Amount,

Amount/Day(MonthEnd(Date#(MonthYear, 'MMM-YYYY'))) as AmountPerDay;

LOAD * Inline [

MonthYear, Amount

Nov-2016, 64523214.21

Dec-2016, 96369197.32

];