AVG Aggregation on top of Calculation view does not work

[former_member457065]

Hi Experts,

I need clarification regarding behavior of aggregation on top of Calculation view.

I have 2 Calculation views - in the first view there is aggregation node with 2 attributes and 2 measures and the Aggregation Type is Sum. This one works fine - it sums measures grouped by those two attributes.

This is Calculation View 1:

In the second view there is a projection node which holds the above mentioned Calculation view 1, and Aggregation node where there is one of the attributes from the first view and both measures. As per requirement, Aggregation type is set to AVG.

This is Calculation view 2

Expected results of the second view are average values grouped by the attribute which is in the Aggregation node of Calculation view 2. But instead of average values, actual results display sum values. The same appears if I change Aggregation Type in Calculation view 2 to MAX, MIN or any other. It seems that aggregation node on top of Calculation view always calculates sum regardless of the Aggregation Type set.

I also tried to get average results directly on Calculation view 1 using SQL Column View in _SYS_BIC schema by applying average aggregation in SQL like this:

select IS_URBAN, AVG("DISTANCE"), AVG("TIME") from "_SYS_BIC"."Package_name/Urban"

GROUP BY IS_URBAN

and I'm getting sum results again. Same with max, min.

Then I created a table like this just to check:

CREATE TABLE TEST AS (SELECT * FROM "_SYS_BIC"."Package_name/Urban")

and then I applied the same aggregation on the table:

select IS_URBAN, AVG("DISTANCE"), AVG("TIME") from TEST GROUP BY IS_URBAN

and voila it works! It shows average values.

Why doesn't aggregation work upon view?

I tried some of the suggestions from forums to set Transparent Filter to TRUE on attribute but it didn't help.

This is very strange behavior of aggregation on top of already aggregated values.

Could anyone assist?

Thank you in advance,

Olja

[ruchi_jain_21]

Hi olja.sobajic , I am also facing same issue. Did you get any solution for it?

Regards,

Ruchi

[lbreddemann]

This looks like a case of aggregation level reduction in HANA calculation views.

Check Usage of “Keep Flag” and the material referenced in it for details.