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

42.87079519

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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply