This blog is about CDS scalar functions - CALENDER_SHIFT and CALENDER_OPERATIONS and how to use it in analytical queries for time comparison. Similarly FISCAL_CALENDAR_SHIFT and FISCAL_CALENDAR_OPERATION can be used.
These CDS scalar functions are used to compare the measure value of current time period with previous time period. Further it can be used to calculate measure value for year to date (YTD), month to date (MTD) or quarter to date (QTD). Current member can only be used when time dimensions are in drilldown else restricted measure is used. The usage of current member is not as fast as restricted measure. Detail specification of the mentioned scalar functions can be found in Help.
Usage of Current Member
This function can be used by modeling an association with the cube. In the ON-condition, the scalar functions are specified on the right side.
association to ZOQ_FLIGHT as _previousMonth
on _previousMonth.fyearMonth = calendar_shift( base => $projection.fyearMonth,
base_level => calendar_date_level.#MONTH,
shift => abap.int2'-1',
shift_level => calendar_date_level.#MONTH )
Constraints:
◉ Current member to work properly, we have to mark all the time fields which are related with annotation - @ObjectModel.value.derivedFrom. We should also specify semantics. for example,
key fldate,
@ObjectModel.value.derivedFrom: 'fldate'
@Semantics.calendar.yearMonth: true
@ EndUserText.label: 'FL Month'
_calday.calmonth as fyearMonth,
◉ The base parameter must be filled with the same field name as it appears on the left: for example, the field name __previousMonth.fyearMonth should be same as base => $projection.fyearMonth
◉ The Semantics of field $projection.fyearMonth must match the base_level parameter. As In the cube view example, the fyearMonth field has the annotation @Semantics.calendar.yearMonth: true, base_level is specified as calendar_date_level.#MONTH.
◉ In the example to restrict the measure to the previous month, shift is set to -1 and shift_level is set to #MONTH. There is a constraint in using shift_level with base_level : for example for base level #MONTH, shift _level can be #MONTH,#QUARTER or #YEAR But can’t be #DAY.
Example 1: Time comparison with time in drill-down (Current Member Variables)
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Query: Current Memeber on Month'
define transient view entity ZLR_PC_CM_1
provider contract analytical_query
as projection on ZOQ_FLIGHT
association to ZOQ_FLIGHT as _previousMonth
on _previousMonth.fyearMonth = calendar_shift( base => $projection.fyearMonth,
base_level => calendar_date_level.#month,
shift => abap.int2'-1',
shift_level => calendar_date_level.#month )
association to ZOQ_FLIGHT as _firstMonthOfQuarter
on _firstMonthOfQuarter.fyearMonth between calendar_operation( base => $projection.fyearMonth,
base_level => calendar_date_level.#month,
operation => calendar_date_operation.#first,
operation_level => calendar_date_level.#quarter )
and $projection.fyearMonth
association to ZOQ_FLIGHT as _CMM1_CMP1
on _CMM1_CMP1.fyearMonth between calendar_shift( base => $projection.fyearMonth,
base_level => calendar_date_level.#month,
shift => abap.int2'-1',
shift_level => calendar_date_level.#month )
and calendar_shift( base => $projection.fyearMonth,
base_level => calendar_date_level.#month,
shift => abap.int2'1',
shift_level => calendar_date_level.#month )
{
@AnalyticsDetails.query: {
axis: #ROWS,
totals: #SHOW
}
@EndUserText.label: 'Year Quarter'
flquarter,
@AnalyticsDetails.query: {
axis: #ROWS,
totals: #SHOW
}
@EndUserText.label: 'Year Month'
fyearMonth,
@AnalyticsDetails.query.axis: #FREE
.hidden: true
currency,
@Semantics.amount.currencyCode: 'currency'
paymentsum,
_previousMonth.currency as PrevMonthCurrency,
@Semantics.amount.currencyCode : 'PrevMonthCurrency'
@EndUserText.label: 'Previous Month'
_previousMonth.paymentsum as PrevMonthPayment,
_firstMonthOfQuarter.currency as firstMonthOfQuartCurrency,
@Semantics.amount.currencyCode : 'firstMonthOfQuartCurrency'
@EndUserText.label: 'Quarter to today'
_firstMonthOfQuarter.paymentsum as firstMonthOfQuartPayment,
_CMM1_CMP1.currency as last3MonthCurrency,
@Semantics.amount.currencyCode : 'last3MonthCurrency'
@EndUserText.label: 'Previous month To next month'
_CMM1_CMP1.paymentsum as CMM1_CMP1Payment
}
where flyear = '2019'
and currency = 'EUR'
Result:
In this Result
◉ Red - column Previous Month -shows value from previous month.
◉ Yellow - column Quarter to Today- shows the aggregate values of previous months in this quarter.
◉ Green - column Gliding window of 3 month- shows aggregate value of previous month + current month + next month.
Usage of Restricted Measure
This can be done using the CASE statement, where one of the scalar function is used.
case when fyearMonth = calendar_shift( base => $parameters.p_month,
base_level => calendar_date_level.#MONTH,
shift => abap.int2'-1',
shift_level => calendar_date_level.#MONTH ) then paymentsum end as paymentPrev
Constraints:
- The base parameter must be a parameter or a literal.
- The type of parameter or literal must match to base_level. In the example for base_level calendar_date_level.#MONTH, the type of parameter p_month must be NUMC6.
- In the example to restrict the measure to the previous month, shift is set to -1 and shift_level is set to #MONTH.
Example 2: Time comparison (Restricted key figure)
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Query: Current Memeber on Month'
define transient view entity ZLR_PC_RSRT_SHIFT1
provider contract analytical_query
with parameters
p_month : /bi0/oicalmonth
as projection on ZOQ_FLIGHT
{
@AnalyticsDetails.query: {
axis: #ROWS,
totals: #SHOW
}
carrid,
currency,
virtual currCur : abap.cuky,
@Semantics.amount.currencyCode : 'currCur'
@Consumption.dynamicLabel: {
label: 'Bookings &1',
binding: [{ index : 1 , element : 'fyearMonth' }]
}
case when fyearMonth = $parameters.p_month then paymentsum end as paymentCur,
virtual currPrev : abap.cuky,
@Semantics.amount.currencyCode : 'currPrev'
@Consumption.dynamicLabel: {
label: 'Previous Bookings &1',
binding: [{ index : 1 , element : 'fyearMonth' }]
}
case when fyearMonth = calendar_shift( base => $parameters.p_month,
base_level => calendar_date_level.#month,
shift => abap.int2'-1',
shift_level => calendar_date_level.#month ) then paymentsum end as paymentPrev,
virtual currLastInYear : abap.cuky,
@Semantics.amount.currencyCode : 'currLastInYear'
@Consumption.dynamicLabel: {
label: 'Bookings from &1 To &2',
binding: [{ index : 1 , element : 'fyearMonth', replaceWith: #LOW },
{ index : 2 , element : 'fyearMonth', replaceWith: #HIGH }]
}
case when fyearMonth between calendar_operation( base => $parameters.p_month,
base_level => calendar_date_level.#month,
operation => calendar_date_operation.#first,
operation_level => calendar_date_level.#year ) and $parameters.p_month then paymentsum end as paymentToDate
}
where currency = 'EUR'
Result:
In this example I have used @Consumption.dynamicLabel to dynamically set the column label with values calculated from parameter.
No comments:
Post a Comment