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:
HELPER@base = IF(ISNULL(“KF2AVG@base”), 0, KF2AVG@base)
HELPER@REQUEST = AVG( “HELPER@base”)
KF2AVG@REQUEST = HELPER@REQUEST
This is a way to do it, but you could find a better implementation. Just make sure you consider the peculiarity of AVG and that you are clear with what your use case requires.