1

# HANA Calculation View Aggregation for sub Total

Oct 17, 2017 at 07:11 AM

210

Former Member

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?

Thanks
Mirana

agg-max.jpg (36.2 kB)

While the answers given so far are correct, what interests me is this: what is the use case for this query?

Why do you put a MAX() aggregation right next to a SUM() aggregation and what does the reader of this grid makes of it?

Also, I noted that the "Total Result" for expected output is the sum of all max values ... can you explain how this makes any sense?

Former Member
Lars Breddemann

Dear Lars,

This is a sample Query. I just created sample table and inserted few dummy data before come to real scenarios. our requirement is similar to what i have depicted below.

as an example, lets take EMPLOYEE_TYPE = 1 where it has 3 employees , that is, Mirana, Venura and Kithsiri and the highest salary will be 50000.00 which is Kithsiri and EMPLOYEE_TYPE = 2 highest salary obtainer is Venura which is 30000.00 etc.

the highest Salary has been depicted on column Name SALARY_max which shows highest salary obtained.

here, the column name SALARY_max has aggregation type as MAX, that is why you see Total result as 50000.00 in SALARY_max column and Output column has Aggregation Type as SUM which produce Total Result as 225000.00 which is wrong as per our requirement.

our requirement is that EMPLOYEE_TYPE wise total which are 50000, 30000 and 45000 in SALARY_max column should produce total result as 125000.00.

in column SALARY_max we have expected result but Total Result should be sum of all max values as in Expected Output.

Thanks

Mirana

capture.jpg (51.5 kB)

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.

Thanks
Mirana

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).

Former Member
Oct 17, 2017 at 02:00 PM
1

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

Thorsten

Show 1 Share
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.

Thanks
Mirana

Former Member Oct 17, 2017 at 06:55 PM
1

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.

Thanks,

Anup

Show 1 Share
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

Thanks
Mirana

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