Technical Articles
Achieving Calculation Before Aggregation and Calculation After Aggregation in SAP HANA Modelling using Projection and Aggregation Views
Introduction:
While doing Calculation that involves Aggregation in SAP HANA Modelling, we would have come across scenarios where we would need to achieve Results based on Calculation before Aggregation and Calculation after Aggregation. In this blog post, I would be briefly explaining how to achieve this in SAP HANA Graphical Calculation View Modelling.
Main Problem:
As per the requirement of one of my customers, while developing a Production Planning Report, I wanted to calculate Expected Production based on a formula, which is the product of two fields namely machine speed and actual available time.
In order to achieve this, I have created a Calculated column in Projection View and applied the formula as shown in the below screenshot:
When Date field is used, Expected Production is calculated as per the formula against each Date field.
When Date field is removed, Expected_Production should be 537.84 (=7.47*72)(Since Expected_Production should be calculated after aggregating Actual_Available_Time (2.5+2.48+2.49=7.47) and Machine_Speed (24+24+24 = 72)). But we have got the result as 179.28. This is due to Calculation before Aggregation.
Solution:
So, in order to perform Calculation After Aggregation, I have created a calculated column in aggregation before semantics.
With date field BUDAT included, both the calculated columns namely Expected_Production_Aggregation_SUM and Expected_Production_Projection_SUM fetch the same values.
On removal of Date field BUDAT (in my case), you can now notice that the Calculated column in aggregation node performs Aggregation after Calculation.
Thus, we have achieved the expected result in Expected_Production_Aggregation_SUM.
Conclusion:
So, from this blog post you could have got an idea on how to achieve Calculation before Aggregation and Calculation after Aggregation in SAP HANA Modeling. Kindly feel free to post your comments and reviews about this blog.
Reference:
- ‘Aggregation Behavior Calculation View SAP HANA’ from the link provided below: https://answers.sap.com/questions/192870/aggregation-behaviour-calculation-view-sap-hana.html
Good explanation Bhavya 🙂
But isn't 179.2 is the correct way of doing the math given your case" ?
Hi Sreehari, we have achieved 537.84 in Expected_Production_Aggregation_SUM column as a result of calculation after aggregation.
given the case , what is the correct result(without date field in selection) ? 537.84 or 179.2 ?
I would say 179.2 .
lets say I drive at
100KMPH for 1 hr in day 1 => total distance is 100km
70kmphr for 2 hrs in day 2 => 140 km
10kmph for 10 hrs in day 3 => 100km
actual total distance travelled is 340 km .
and not ( 100 + 70 + 10 ) * ( 1 + 2 + 10) = 2340 km .
Or did I mistook the logic :-/
Hi Bhavya
There was an option - checkbox available in Analytic view "Calculate before aggregation" during creation of a calculated column.but your explanation is based on Calculation view where this option ("Calculate before aggregation") doesn't available. Probably this option not present in Calculation view due to availability of Aggregation node.
So could anyone please explain how an Analytic view behave if we won't select the checkbox("Calculate before aggregation") in Analytic view ?