Calculation after aggregation in ABAP CDS ( Percentage calculation )

[sreehari_vpillai]

I am not sure if I am thinking in the right direction.

I developed a CDS which has aggregated measures like

 @EndUserText.label: 'Total Invoiced Amount'
    @Semantics.amount.currencyCode: 'CurrencyKey'
    @DefaultAggregation: #SUM 
    invoice_value as InvoiceAmount

I exposed this CDS to OData later using SADL( and later mapped to a smart chart in SAPUI5 ) . Now, the consumers can choose the dimensions and measures on the go so that aggregation will be handled dynamically.

Here comes the question - I want to run a percentage calculation on aggregated data .

Below is the output when I have year and month as dimensions.

When I change the dimension to Year alone, the expected output is

Note( for people like me ) : average of percentages in table 1 not equal to actual loss % in image 2( which is the right figure )

Question in a glance : How can I have this calculation executed post aggregation of related measures ( in our case , expected sales value and Invoice value ) .

Alternatives i tired -

I the DPC_EXT , i redefined the get_entityset method and called super method. Later looped the response and calculated the percentage in the line item level . Issue is , the super method will only populate the slected columns from the odata $select . That is, if I choose to display only Year and Loss % , it will not return total sales value and Invoice value columns ( standard feature )

Sreehari

[deodutt_dwivedi]

Hi Sreehari,

You can try exception aggregation in the CDS query which you are exposing to odata. Set the level @ Year, Month and then you can tweak the aggregation behaviour further. You can refer to below wiki page for a sample exaple.

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

Regards,

Deo

[sreehari_vpillai]

Hay.

My CDS is not a query . Below is my annotation list.

@AbapCatalog.sqlViewName: 'ZSQLC01'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Sales performance'
@Search.searchable: true

Sree.

[deodutt_dwivedi]

Hi Sreehari,

I think the kind of calculation you are trying to achieve is possible via exception aggregation as far as i know. Any challenges in creating a query CDS view on top of your current view and then doing the calculation and then exposing to ODATA?

Regards,

Deo

[sreehari_vpillai]

I will check the possibilities of using a query CDS . The current CDS is dealing with trillions of records aggregation .I am bit hesitant to nest my views 😞

Sreehari

[maheshpalavalli]

Hi Sreehari V Pillai,

This looks tricky.. I hope your issue is that the calculation you are doing in the CDS view is applied before the aggregation and while fetching the data, it is averaging the total percentages and giving to you..

I do not know how to solve this issue but for the "Alternatives i tired", which you have mentioned, If you are using a smart chart directly in your custom fiori app, you have the option to pass "requestAtLeastFields" or PresentationVariant.

May be if you use this, you can get the select for the invoice value and loss so you can do the same in DPC_EXT.

*** UPDATE ***

Have you tried this ? apparantly if you use the formule default aggregation, it will only be applied once after the aggregaton is done.

BR,
Mahesh

[sreehari_vpillai]

Something is stopping me from using "requestAtLeastFields" property ( may be my UI library version - which I will explore and post here ) . Now I am managing in DPC_EXT , assuming the user is requesting all the related measures to calculate the percentage. Actually its again illogical !

Say my loss of sale is 10,000.00 USD(1) and expected sale is 200,000.00 USD(2). % value will be between 0-100(3) . Plotting these 3 values together in a chart ( as i am requesting all the 3 measures from smart chart ! ) doesn't show the % value ( as its too small to plot long with other values ). This is the reason I am expecting to request only the % of loss of sale alone from smart chart

Note : i am not sure requestAtLeastFields measures will be plotted along with the manually selected measures from the smart chart variant . Will check this too.

Coming to the DefaultAggregation : #FORMULA annotation - is applicable only when its exposed as an analytic query ( Isn't it ? ever tried this ? )

thanks for your time to check.

Sreehari

[maheshpalavalli]

Ohhhh, maybe you can try using the presentation variant annotation in the UI5 app for this..

Yeah, I am also not sure if the atLeasFields will show up in the chart.. I am guessing it might not.. as I've done the same for the smart table some time back..

I think the annotation supports only the analytical query as per the SAP docu..

BR,

Mahesh

[sreehari_vpillai]

^^ let me give a try on requestAtleastFields. I have hope here

Sree