Skip to Content
avatar image
Former Member

HANA Calculation View Aggregation for sub Total

Hi all,

following is my issue

  • I just want to have node wise Aggregation as MAX and subtotal should be addition of all the separate node

As an example,

  • I managed to get MAX value by EMPLOYEE_TYPE which is being shown correct values in output column (please check insteted Image).
  • But, Employee type wise result giving me total of all EMPLOYEE_TYPE.

(as an example EMPLOYEE_TYPE 1 has three Employees. they are Mirana, Venura and Kithsiri and it shows max value as 50000.00 but you can see result is 150000.00 which is a total of all three Employes)

But, Expected output which i have manually added as column name Expected Output . this should be shown as max value for all the EMPLOYEE_TYPE and Total Result should be as 125000.00 which is a addition of (50000.00 + 30000.00 + 45000.00 = 125000.00)

what should be the way to answer this?


agg-max.jpg (36.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Thanks for that extended explanation of what you want to compute. Mirana.

    What I wanted to know, though, was what business scenario is behind summing up the highest salaries of every employee type group?

    I completely get the calculations that you like to perform, but I don't understand why you are doing them. What decision can I make when I know that the adding up the highest salary amount for every group is 125.000?

  • Former Member Lars Breddemann

    Hi Lars,

    it is not all about Employee Salary. But, our requirement has been depicted with above example.

    Real requirement is as follows.(please refer attached screenshot)

    here, you can see Sales Order 2000047637 has two line items which are 000010 and 000020.

    MfgOrderItemGoodReciptQty is working as we expected (Please check column name MfgOrderItemGoodReciptQty).

    But, when it comes to OrderQuantity and NetAmount column, you can see order quantity for line item 000010 is 8640.00 and net amount is 3110.40 also for line item 000020, it is 54144.00 and 82298.88 respectively.

    Here, You can see same values have been repeated for each ManufacturingOrder and it produces result as 51840.00 for OrderQuantity and 18662.40 for NetAmout for line item 000010 and same as for line item 000020, which produce wrong Result.

    yes, if you have Aggregation Type as MAX, then of course you will see the correct values for line item 10 and 20.

    But, total result will again produce wrong output. it will be the MAX value for two columns which is 54144.00 for OrderQuantity and 82298.88 for NetAmount

    I have manually added expected two columns

    Hope, you are clear about the Requirement.


    capture3.jpg (124.8 kB)
  • Ok, I see.

    In fact, you are not actually after the MAX() of OrderQuantity or NetAmount at all. What you want is to use these values as reference values for all rows in the same group when grouped by SalesDocument and SalesDocumentItem.

    Only upon aggregating the groups, the values should be summed up.

    See, that is a very different requirement from what you described and a good example for why it is important to describe what and why you want to do something, instead of sticking to techniques and tricks you use to achieve your goal.

    In SAP BW terms, what you are looking for here is a "constant selection", which means that you can have cells in your grid that have a different aggregation level than the cells next to them. That's clearly nothing that SQL or the relational DB model support.

    Therefore, you can approximate this behaviour either by using a client tool that supports this or by constructing a result set by combining multiple aggregations e.g. via joins (as mentioned by the other commentators).

  • Get RSS Feed

2 Answers

  • Oct 17, 2017 at 02:00 PM

    Hi Mirana,

    there is no way to do this in the calculation view, you only can do this in your Frontend e.g. the Analysis for office with a separate calculation.

    Normally, there is a functionality in AoF in tab "Analysis -> Totals -> Calculate Totals as", but for the use with Hana views unfortunately
    this functionality is not active.

    Best Regards


    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      hi thorsten,

      yes, you are right, it is inactive.

      this situation can be managed with Webi Reporting. but customer wants to see this AO as well.


  • avatar image
    Former Member
    Oct 17, 2017 at 06:55 PM

    Hi Mirana,

    You can solve using following steps:

    1. Create a scripted calculation view similar to your existing view but without employee name. In your SQL script, use following logic:

    SELECT MAX (<<Your key figure>>), <<ALL other columns except employee>> from XXX group by employee type.

    2. Join this scripted calculation view with your existing calculation view on employee type and read the measure from scripted calculation view.



    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Anup,

      that's how i did but with graphical view.

      column SALARY_max (screen 02:please check attached file) shows expected result but Total result is wrong as per our requirement, which produce Total Result as 50000.00 .

      it should be sum of all MAX values which is 50000+30000+45000 = 125000

      i have attached screens for your reference

      screen 01

      screen 02


      capture1.jpg (60.1 kB)
      capture.jpg (51.5 kB)