Skip to Content
Technical Articles
Author's profile photo bhavyasree m

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:

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Muralikrishna P
      Muralikrishna P

      Good explanation Bhavya 🙂

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai

      But isn't 179.2 is the correct way of doing the math given your case" ?

       

      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.

      Author's profile photo bhavyasree m
      bhavyasree m
      Blog Post Author

      Hi Sreehari, we have achieved 537.84 in Expected_Production_Aggregation_SUM column as a result of calculation after aggregation.

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai

      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 :-/

       

      Author's profile photo Siba Panda
      Siba Panda

      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 ?