Calculate Average in Analytics Engine (Core Data Services + Analysis for Office)

[former_member38556]

Introduction: This post is a short explanation for an ABAP Developer to understand how to achieve tricky aggregations in their developments.

Details: I recently had one requirement where user wanted to display SUM as well as AVERAGE for certain measures based on "customer" as a unique dimension.While defining cube view,it is known that measures are separated by dimensions by means of certain annotations.

For having SUM as aggregation we use @Default Aggregation for SUM and thus,while consuming the final consumption view in tools like Analysis for Office,we get the SUM based on the "Key" fields formed in the excel. The annotation is defined as shown below:

@DefaultAggregation: #SUM

For understanding default aggregation please consider this blog:

https://wiki.scn.sap.com/wiki/display/BI/Default+Aggregation+in+CDS

The problem which I faced was,while achieving aggregation AVERAGE,it shows me an error as attached in the image below.

To solve this issue,we can follow below methods:

Method 1: Use exception aggregation on particular fields based on which Average can be calculated as described in the following blog:

https://wiki.scn.sap.com/wiki/display/BI/Simple+CDS+Query+with+Exception+Aggregation+AVG

(Also note that Exception Aggregation can have exception fields up to 5 only)

Method 2: This is specifically for calculating Average.

We know that average = ( SUM of values ) / ( SUM of count of values )

For example : Average of 1,4,6  = ( 1+4+6 ) / ( 3 )

Now this is ( 1+4+6 ) / ( 1+1+1 ) which is nothing but sum of values / total no of values.

To achieve this,

In the cube view,

Use Default Aggregation as SUM for the values. (Numerator which is considered for calculating Average),

Use Default Aggregation as SUM , and add count field. (Denominator which is considered for calculating Average)

//Main field which is to be averaged is been default aggregated as sum

  @DefaultAggregation: #SUM

  NetDaysGranted,



//Count field sum as aggregation

 @DefaultAggregation: #SUM

 1              as Count,

In the consumption view above this,add a formula as below:

@AnalyticsDetails.query.axis: #COLUMNS

@EndUserText.label: 'Net Arrears Days'

@DefaultAggregation: #FORMULA

@AnalyticsDetails.query.formula :'NetDaysGranted / Count'

1            as  NetDaysGranted,

Now go to analysis for office and insert this data source. ( The final consumption view )

We find that average is calculated for this measure.Also to note that SUM is followed first for "NetDaysGranted" and "Count" then AVERAGE is calculated and not otherwise.

( A point to note : "Restrictions" functionality in Analysis for Office is not supported for calculated measures as described above.)

Conclusion: Thus,to summarize this post demonstrates how aggregations(especially AVERAGE) are achieved in Analytical Engine.