CDS View Conditions and Calculations

[fredi0711]

Hello experts,

In a CDS View, I'm trying to calculate for the total amount (YTD) using @DefaultAggregation: #SUM

Initially, I thought of using parameter p_keydate (YYYYMMDD) as reference and just get the substring for YYYY using @Consumption.derivation

and pass it on the condition, however we need to have the functionality to get YTD for multiple entries and that is where I'm currently lost.

Here are some code snippets for what I'm currently doing:

define view ZZ1_EARNINGSV3

with parameters

@Semantics.businessDate.at: true

@Environment.systemField: #SYSTEM_DATE

P_KeyDate : vdm_v_key_date,

@Consumption.hidden: true

@Consumption.derivation: { lookupEntity: 'ZCDS_PER_YTD_ADJUSTED', //this is a custom cds view that selects //from I_CalendarDate and has other additional fields added

resultElement: 'CurrentYear',

binding: [{ targetParameter: 'P_InputDate',

type:#PARAMETER ,

value: 'P_KeyDate' }]

}

p_curryear : abap.numc(4)

as select from ZZ1_EARNINGSV3_BOE

{

//In the CDS View, here is the field to input the Fiscal Year:

@AnalyticsDetails.query.axis: #FREE

@Consumption.filter: { selectionType: #INTERVAL, multipleSelections: true, mandatory: true }

@Consumption.derivation: { lookupEntity: 'I_CalendarDate',

resultElement: 'CalendarYear', binding: [

{ targetElement : 'CalendarDate' , type : #PARAMETER, value : 'P_KeyDate' } ]

}

@AnalyticsDetails.query.variableSequence: 10

LedgerFiscalYear,

//Here is the Part where I'm trying to calculate for the YTD

@AnalyticsDetails.query.axis: #COLUMNS

@EndUserText.label: 'Actual (YTD)'

@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }

@DefaultAggregation: #SUM

CASE when FiscalYear = :p_curryear //NEED TO CHANGE THE CONDITION SO THAT IT WOULD COMPARE TO ALL THE SELECTIONS IN LedgerFiscalYear

then AmountInCompanyCodeCurrency

else cast( '0' as fins_vhcur12)

end as ActualYTD

I couldn't directly use the LedgerFiscalYear ( CASE when FiscalYear = LedgerFiscalYear ) in the condition because I'm getting an error: Expression (at Line 570 Column 25) could not be converted into formula [Analytics]

To summarize: How to incorporate the multiple Values from LedgerFiscalYear into the condition to calculate the amount.