AverageIf function in Dashboards 4.0
In SAP Dashboards 4.0 not all excel functions are supported, for example the function”AVERAGEIF”. When you use this function in your dashboard and bind the cell to a component, the error “Unsupported Excel function: AVERAGEIF” will be displayed. This function can be very useful in some cases. To solve this issue, you can use an alternative.
In the example below I calculate the average profit where the region is equal to *west*. This results in EURO 16.734,- with the AVERAGEIF function. In the picture on the right you’ll find an alternative to calculate the average with the same condition equal to *west*. Use a combination of both functions: “COUNTIF” and “SUMIF”.
This will make your dashboard development much better! Good luck!
Note: Because the COUNTIF and SUMIF are very complex functions, don’t use these functions on large datasets! Thanks to Ryan Goodman for this addition.
We actually recommend that customers be very careful with SUMIF, or COUNTIF because they are highly in-efficient in Xcelsius. For example, I had a customer today who built a dashboard on top of BW, and with a column of 1000 CountIf functions. The dashboard lagged for 15-20 seconds. Without CountIF it went down to less than one second after the query finishes. If used sparingly it is great but watch our for more complex tools.
Hi Ryan,
Thanks for your addition, I agree with you. This solution can be used for a small set of data. I’ll write a note in the post to highlight this subject.
Regards,
Pieter