Find here a quick practical note on the use of the AVG operator, available from the version 3 of S&OP on HANA. Before this version we were working with helper key figures calculating the arithmetic average via SUM operator only. As you will see the concept is straightforward, if you pay attention to it. Overlooking the definition of AVG will get you wrong results and a potential loss of (personal) reputation with your customer.
Typically you will be asked to provide calculations containing simple or arithmetic averages of key-figures. Often when customers ask you that, they mean that the set:
1, NULL, 5 will have as arithmetic average the value of 2.
The AVG, on the contrary will return to you the value of 3. That is because within a database paradigm NULL is a non value.
So, pay attention to that and if for your customer NULL will mean "0" (zero) change your design accordingly.
In my case I am using a helper key figure that "fills in the blanks", something similar to this: