Calculate KPI values for High level view and detailed view using Concatenation+Exception aggregation
This blog will clarify how to resolve the issue related to calculation of KPI for high level view and detailed view
where detailed view is based on drill down of 2 different Infoobjects and this combination is unique key :-
For Example:
we have a division calculation for one KPI : (Unit charge/Consumption * 100 ).
High level view:
Account Determination ID |
Unit charge Value |
Consumption | Unit charge/Consumption |
Commercial Customers |
275.9100000 | 1,872.300 | 14.73642045 |
Detailed view :
If we drill down on Installation and date output is :
NOTE: Installations 6000359409 is repeated and date 03/31/2014 is also repeated corresponding to different Installation.
Account Determination ID |
Installation Number |
To Date | Unit charge Value |
Consumption | Unit charge/Consumption |
Commercial Customers |
6000359300 | 03/31/2014 | 194.69000000 | 1172.10000000 | 16.61035748 |
Commercial Customers |
6000359359 | 03/31/2014 | 51.60000000 | 518.60000000 | 9.94986502 |
Commercial Customers |
6000359409 | 02/28/2014 | 15.59000000 | 95.57900000 | 16.31111437 |
Commercial Customers |
6000359409 | 03/31/2014 | 14.03000000 | 86.02100000 | 16.30997082 |
however if we see the sum of last column:
Unit charge/Consumption |
16.61035748 |
9.94986502 |
16.31111437 |
16.30997082 |
Sum is =
59.18130769 |
If we use Exception aggregation alone , it won’t work for example if we aggregate on Installation output will be like below :-
Account Determination ID |
Installation Number |
To Date | Unit charge Value |
Consumption | Unit charge/Consumption |
Commercial Customers |
6000359300 | 03/31/2014 | 194.69000000 | 1172.10000000 | 16.61035748 |
Commercial Customers |
6000359359 | 03/31/2014 | 51.60000000 | 518.60000000 | 9.94986502 |
Commercial Customers |
6000359409 | 02/28/2014 | 29.62000000 | 181.60000000 | 16.31057269 |
Sum = |
How to achieve this in BW :-
1. Concatenation:
To achieve this we have to create a new Infoobject of length equal to sum of both these Infoobjects and get concatenated value into this new object :-
2. Exception agreegation
Now apply exception aggregation on
Unit charge/Consumption calculation based on this new Infoobject
Output will be displayed like below :-
High level view:
Account Determination ID |
Installation Number and Date |
Unit charge Value |
Consumption | Unit chare/Consumption |
Commercial Customers |
600035930020140331 | 194.69000000 | 1172.10000000 | 16.61035748 |
Commercial Customers |
600035935920140331 | 51.60000000 | 518.60000000 | 9.94986502 |
Commercial Customers |
600035940920140228 | 15.59000000 | 95.57900000 | 16.31111437 |
Commercial Customers |
600035940920140331 | 14.03000000 | 86.02100000 | 16.30997082 |
Detailed view :
Account Determination ID |
Unit charge Value |
Consumption | Unit chare/Consumption |
Commercial Customers |
275.9100000 | 1,872.300 | 59.18130769 |
Now sum is matching.