Rolling 3 month Avg for latest month -RangeAvg()

[chaper]

I am trying to get the Sliding/Moving/Rolling 3 Month Avg of latest month.When I use rangeavg (above(field,1,3)) in a straight table i am getting correct values for all months.But when I try to get  Rolling 3 Month Avg of latest month only for ex:Aug 15 into a variable using same expression then it is not working as in Straight table .Even if I manually enter/select the Month in the RangeAvg expression it is not working.Need help in getting Rolling 3 Month Avg of latest month in a variable??

Thanks

Chaiperi

[Anonymous · Accepted solution]

Hi Chaiperi, the 'above' function will not work in a variable as there are no grouped above lines the variable can use.

rangeavg uses a list of values so you just have to get each of the last three months into the array to perform the calculation.

So rangeavg(sum({<Month = {1}>}Amount),sum({<Month = {2}>}Amount),sum({<Month = {3}>}Amount)) will work. You can use the inmonth function to calculate the last three months automatically. I can send more details on this if required.

Hope this helps.

[Digvijay_Singh]

Check this out, it is showing same value, see if you can make out what different you are doing..

[Digvijay_Singh]

I might misunderstood the problem

[Gysbert_Wassenaar]

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

[chaper]

thanks for the reply.Can you send  more details ???

[chaper]

thanks for reply.In my case I am using rangeavg (above(Sales,1,3)) instead of rangeavg (above(Sales,0,3)).If I use rangeavg (above(Sales,1,3))  it shows null in variable

[Digvijay_Singh]

Are you ok with the results of rangeavg (above(Sales,1,3)), I think that would start from previous record upward, 0 will start from current row. First let us know if your real problem is something what I understood or what chaiperi understood. I suggest go through the link gwassenaar‌ for complete understanding of rolling totals.

[Digvijay_Singh]

Sorry, I meant for Jeremy123‌ in my previous post while asking for clarification, wrong address copied.

[Anonymous]

For sure so have a look at the inmonth or inmonthtodate functions. The definition is as follows:-

InMonth (date, basedate , shift)

• date is the field in your data.
• basedate is the date (usually entered using a function like today() or entered through a variable) for which you want to find the information for. So use today's date there to get an answer of true for all of the days in this month.
• shift you can use it to find a different month (-1 will be one month prior to the date you are using in basedate).

The function returns a value of -1 where the values are in the month.

So your rangeavg for the 3 month rolling could work like.:-

rangeavg(sum(-InMonth ([date], today() , 0) *Amount),sum(-InMonth ([date], today() , -1) *Amount),sum(-InMonth ([date], today() , -2) *Amount))

I find this function really helpful.

An alternative to all this is to build a latest 3 month special period into your calendar which will be a fantastic solution too.