Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Check this out, it is showing same value, see if you can make out what different you are doing..
I might misunderstood the problem
Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations
thanks for the reply.Can you send more details ???
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
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.
Sorry, I meant for Jeremy123 in my previous post while asking for clarification, wrong address copied.
For sure so have a look at the inmonth or inmonthtodate functions. The definition is as follows:-
InMonth (date, basedate , shift)
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.