Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Sum last 12 months - moving average

Hello all,

I succeed in calculating the sales of the last 12 months but if I want this calculation in a table for each month it does not work.

So in the month september I want to see sales of the last 12 months, starting from september, In the month august I want to see sales of the last 12 months, starting from August and so on.

This is the formula I used to calculate sales of the last 12 months

=sum({\$<Jaar_Kalender=, Maand_Kalender=, YTD_Flag=, %Key_Datum={'>=\$(=vStartDate)<=\$(=vMaxDate)'},Eigen_Merk={'Nee'}>} Waarde_Levering)

where vStartDate=date(Today()-365,'DD/MM/YYYY')

where vMaxdate=max(%Key_Datum)

As soon as I add a dimension Month-Year I get sales per month and not the cumul of the previous 12 months...

Can someone help ?

Peter

4 Replies

If it is straight table then you can use Accumulate radio button or else try this

Rangesum(Below(sum({\$<Jaar_Kalender=, Maand_Kalender=, YTD_Flag=, %Key_Datum={'>=\$(=vStartDate)<=\$(=vMaxDate)'},Eigen_Merk={'Nee'}>}Waarde_Levering),0),Above(Waarde_Levering,1, RowNo(TOTAL)))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Contributor III
Author

It does not seem to work.

I still get only the sales of the month :

Where I should have the total of 23.266.764 in sep-2017

Peter

Specialist III

Dear Peter,

Can you Try

=RangeAvg(Above(Sum(Waarde_Levering), 0, 12 ))

=RangeSum(Above(Sum(Waarde_Levering), 0, 12 ))

MVP

Or try this

=RangeSum(Above(Sum({\$<Jaar_Kalender=, Maand_Kalender=, MaandJaar_Kalender=, YTD_Flag=, %Key_Datum=,Eigen_Merk={'Nee'}, >} Waarde_Levering), 0, RowNo())) * Avg(1)

Community Browser