Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
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

12 Replies
kroberts13
Contributor III
Contributor III
Author

Sunny,

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

kroberts13
Contributor III
Contributor III
Author

Sunny,

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

sunny_talwar

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

];