Using 12 steps back for Moving Annual Total (MAT) when some months are "empty"

Anonymous — Thu, 10 Mar 2011 18:29:38 GMT

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.

Thank you in advance

Re: Using 12 steps back for Moving Annual Total (MAT) when some months are "empty"

itec_pao — Mon, 09 Apr 2018 11:49:30 GMT

Hi David,

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:

The As-Of Table

Additionally, by using DateDiff (difference between the two dates), you might create different flags for flagging current 3months, 6months, 1Year, Ytd, etc.

Regards,

Oktay

topic Using 12 steps back for Moving Annual Total (MAT) when some months are "empty" in QlikView

Using 12 steps back for Moving Annual Total (MAT) when some months are "empty"

Re: Using 12 steps back for Moving Annual Total (MAT) when some months are "empty"