The hierarchy rollup requirement for one of our metrics is non-standard and I can’t figure out how to create a custom aggregate function. In our business, there are retail stores that roll up to a district. Some stores have a blank metric value; some stores have a 0 metric value; and some stores have a normal metric value (always something less than 100). The rollup metric at the district level is defined as the average of all scores greater than 0 - so I can’t use the normal AVG function. In Oracle I could code it as:
SUM(metricvalue) / SUM(CASE WHEN metricvalue>0 THEN 1 ELSE 0 END)
but in metric designer, it appears that you must select from a hard-coded list of rollup functions (AVG, LAST, MAX, MIN, etc).