Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
kroberts13
Contributor III
Contributor III

Rolling Average per day of month

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

1 Solution

Accepted Solutions
sunny_talwar

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

];

Capture.PNG

View solution in original post

12 Replies
sunny_talwar

May be look into: The As-Of Table

kroberts13
Contributor III
Contributor III
Author

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

sunny_talwar

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

kroberts13
Contributor III
Contributor III
Author

Capture.PNGI 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.

sunny_talwar

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

kroberts13
Contributor III
Contributor III
Author

Essentially, yes

sunny_talwar

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

];

Capture.PNG

kroberts13
Contributor III
Contributor III
Author

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!