Using 12 steps back for Moving Annual Total (MAT) when some months are "empty"
Hi all, please help if you can.
I need to use MAT calculations and 12 steps back works great - almost. Sometimes I have no sales in a month for a product but then the 12 steps back just skips and looks for 12 where it finds something. This is incorrect. If I tick show all values in my chart although the chart then works as expected it over rides set analysis and selections on date which is also an issue. I think a solution is to use the following logic
If(Sum of billings = 0, 0.0000001,Sum of billings) where the error is very small but at least the 12 steps will see something and then I can use date selections and set analysis.
BUT - I can't get the If and Sum syntax to work.
Other solution would be to use Rangesum maybe but that seems even harder to write.
I know MAT is common so hope others have fixed this already but couldn't find an answer on the forum.
I have come across a solution, in order to provide a good moving annual total (MAT) analysis. The solution is called As-Of table. A very good example is provided here by Henric Conström:
Additionally, by using DateDiff (difference between the two dates), you might create different flags for flagging current 3months, 6months, 1Year, Ytd, etc.
.png "itec_pao"){kind=avatar}
