Technical Articles

Uladzislau Pralat

June 28, 2019 11 minute read

Comparative Analysis with S/4 HANA Embedded Analytics

Comparative Analysis is a common requirement, for example, Y2Y. The challenge is to model CDS View in such a way that make data selection flexible and easy for both reporting period and reference period.

Below are two examples of comparative analysis in Query Browser. One for QTD and another one for YTD. QTD 2019 and FTD 2019 are compared respectively with QTD 2018 and FTD 2018. What user needs to enter is just rolling period name (Date Function) and CDS view calculates both Seat Occ Rate for selected period and Seats Occ Rate Ref for reference period (selected period offset by either 365 or 366 days).

The same examples of QTD and YTD comparative analysis in Smart Business:

CDS views are modeled in such a way that:

There are two sets of measures one for reporting period and another one for reference period;
Reporting period is selected by Date Function (rolling period) which is converted to date range selection by C_SglGregorianCalDateFunction
Date Function parameter value help is assigned for ease of use;
ZX_CalendarDate View Extension and ZI_CalendarDate Basic View are used to offset reporting period date range by either 365 or 366 days to get reference period date range

See following blogs for more information:

Date Function for dynamic date filtering in Fiori apps

How to add value help for parameter in ABAP CDS Analytical Query

Following CDS views need to be created for the demo:

ZI_DateFunction date function value help view
ZX_CalendarDate calendar date extension view
ZI_CalendarDate calendar date basic view
ZSAPBC_Carr carrier basic view
ZSAPBC_Region region basic view
ZSAPBC_CarrText carrier text view
ZSAPBC_CarrDimension carrier dimension view
ZSAPBC_RegionHierarchy region hierarchy view
ZSAPBC_RegionDimension region dimension view
ZSAPBC_FlightFact flight fact table view
ZSAPBC_FlightCube flight cube
ZSAPBC_FlightQuery flight query

ZI_DateFunction Date Function Value Help View

@AbapCatalog.sqlViewName: 'ZIDATEFUNC'
@EndUserText.label: 'Date Function'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #BASIC
define view ZI_DateFunction as select from C_GregorianCalDateFunction( P_Language: $session.system_language )
{
  DateFunction
}

Note: I_CalendarDate view is extended with CalendarYearMinus1 field. This field will be used late on for leap year offset

ZX_CalendarDate Calendar Date View Extension

@AbapCatalog.sqlViewAppendName: 'ZXCALDATE'
@EndUserText.label: 'Date'
extend view I_CalendarDate with ZX_CalendarDate
  association [0..1] to I_CalendarYear as _CalendarYearMinus1 on $projection.CalendarYearMinus1 = _CalendarYearMinus1.CalendarYear 
{
  @ObjectModel.foreignKey.association: '_CalendarYearMinus1'
  case when calendaryear = '0000'  
       then calendaryear
       else cast(cast(cast(calendaryear as abap.int2) - 1 as abap.char( 12 ) ) as abap.numc( 4 ) ) end as CalendarYearMinus1,
  _CalendarYearMinus1 
}

ZI_CalendarDate Calendar Date Basic View

@AbapCatalog.sqlViewName: 'ZICALENDARDATE'
@EndUserText.label: 'Date'
define view ZI_CalendarDate as select from I_CalendarDate 
{
  key CalendarDate,
  case when I_CalendarDate.CalendarMonth = '01' and _CalendarYearMinus1.IsLeapYear = ' '         
       then dats_add_days( CalendarDate, -365 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '01' and _CalendarYearMinus1.IsLeapYear = 'X'         
       then dats_add_days( CalendarDate, -366 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '02' and _CalendarYear.IsLeapYear = ' ' and _CalendarYearMinus1.IsLeapYear = ' '  
       then dats_add_days( CalendarDate, -365 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '02' and _CalendarYear.IsLeapYear = ' ' and _CalendarYearMinus1.IsLeapYear = 'X'  
       then dats_add_days( CalendarDate, -366 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '02' and _CalendarYear.IsLeapYear = 'X' and I_CalendarDate.CalendarDay = '29'
       then dats_add_days( CalendarDate, -366 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '02' and _CalendarYear.IsLeapYear = 'X'
       then dats_add_days( CalendarDate, -365 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth between '03' and '12' and _CalendarYear.IsLeapYear = ' '  
       then dats_add_days( CalendarDate, -365 , 'FAIL' )
       when I_CalendarDate.CalendarMonth between '03' and '12' and _CalendarYear.IsLeapYear = 'X'
       then dats_add_days( CalendarDate, -366 , 'FAIL' ) end as CalendarDateMinus1Year,
  case when I_CalendarDate.CalendarMonth = '01'             
       then dats_add_days( CalendarDate, -31 , 'FAIL' )     
       when I_CalendarDate.CalendarMonth = '02'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '29' and _CalendarYear.IsLeapYear = ' '
       then dats_add_days( CalendarDate, -29 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '30' and _CalendarYear.IsLeapYear = ' '
       then dats_add_days( CalendarDate, -30 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '31' and _CalendarYear.IsLeapYear = ' '
       then dats_add_days( CalendarDate, -31 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '03' and _CalendarYear.IsLeapYear = ' '
       then dats_add_days( CalendarDate, -28 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '30' and _CalendarYear.IsLeapYear = 'X'
       then dats_add_days( CalendarDate, -30 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '31' and _CalendarYear.IsLeapYear = 'X'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '03' and _CalendarYear.IsLeapYear = 'X'
       then dats_add_days( CalendarDate, -29 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '04'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '05'  and I_CalendarDate.CalendarDay = '31'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '05'
       then dats_add_days( CalendarDate, -30 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '06'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '07'  and I_CalendarDate.CalendarDay = '31'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '07'
       then dats_add_days( CalendarDate, -30 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '08'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '09'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '10'  and I_CalendarDate.CalendarDay = '31'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )              
       when I_CalendarDate.CalendarMonth = '10'
       then dats_add_days( CalendarDate, -30 , 'FAIL' )       
       when I_CalendarDate.CalendarMonth = '11'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '12'  and I_CalendarDate.CalendarDay = '31'
       then dats_add_days( CalendarDate, -31 , 'FAIL' )
       when I_CalendarDate.CalendarMonth = '12'
       then dats_add_days( CalendarDate, -30 , 'FAIL' )       
  end as CalendarDateMinus1Month,   
  CalendarYear,
  CalendarQuarter,
  CalendarMonth,
  CalendarWeek,
  CalendarDay,
  YearMonth,
  YearQuarter,
  YearWeek,
  WeekDay        
}

Note: CalendarDateMinus1Year – whenever offset cross February 29 of leap year then it is required to subtract 366 days instead of 365

Note: CalendarDateMinus1Month – mimics MONTH_PLUS_DETERMINE function module logic in CDS terms. Will be used in second part of the blog

ZSAPBC_Carr Carrier Basic View

@AbapCatalog.sqlViewName: 'ZCARR'
@AccessControl.authorizationCheck:#NOT_REQUIRED
@VDM.viewType: #BASIC 
@EndUserText.label: 'Airline'
define view ZSAPBC_Carr as select from scarr
{
  carrid as CarrID,   
  url as Url,
  currcode as CurrCode,
  cast(case 
  when carrid = 'LH' or carrid = 'AB' then 'Germany'
  when carrid = 'AA' or carrid = 'CO' or carrid = 'DL' or 
       carrid = 'NW' or carrid = 'WA' then 'US'
  when carrid = 'AC' then 'Canada'  
  when carrid = 'AF' then 'France'  
  when carrid = 'AZ' then 'Italy'
  when carrid = 'BA' then 'UK'
  when carrid = 'FJ' then 'Fiji'
  when carrid = 'NG' then 'Austria'
  when carrid = 'JL' then 'Japan'
  when carrid = 'QF' then 'Australia'
  when carrid = 'SA' then 'South Africa'
  when carrid = 'SQ' then 'Singapure'
  when carrid = 'SR' then 'Swirzerland'  
  when carrid = 'UA' then 'US'
  else 'Other'
  end as abap.char( 13 )) as Region    
}

ZSAPBC_Region Region Basic View

@AbapCatalog.sqlViewName: 'ZREG'
@AccessControl.authorizationCheck:#NOT_REQUIRED 
@EndUserText.label: 'Region'
@VDM.viewType: #BASIC
define view ZSAPBC_Region as select distinct from ZSAPBC_Carr {
  key Region,
  case 
  when Region = 'Germany' or Region = 'France' or Region = 'Italy' or
       Region = 'UK' or Region = 'Austria' or Region = 'Swirzerland' then 'Europe'
  when Region = 'US' or Region = 'Canada' then 'North America'
  
  when Region = 'South Africa' then 'Africa' 
  when Region = 'Fiji' or Region = 'Japan' or Region = 'Singapure' then 'Asia'
  else 'Other' 
  end as MainRegion  
}
where Region <> 'Australia'

union

select distinct from scarr {

key 'Europe' as Region,
    'World' as MainRegion
}

union

select distinct from scarr {

key 'North America' as Region,
    'World' as MainRegion
}

union

select distinct from scarr {

key 'Asia' as Region,
    'World' as MainRegion
}

union

select distinct from scarr {

key 'Australia' as Region,
    'World' as MainRegion
}

union

select distinct from scarr {

key 'Africa' as Region,
    'World' as MainRegion
}

union

select distinct from scarr {

key 'World' as Region,
    '' as MainRegion
}

ZSAPBC_CarrText Carrier Text View

@AbapCatalog.sqlViewName: 'ZCARRTEXT'
@Analytics: {dataCategory:  #TEXT, dataExtraction.enabled: true}
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Carrier'
define view ZSAPBC_CarrText as select from scarr {
  key carrid as CarrId,
  @Semantics.text: true
  carrname as CarrierName    
}

ZSAPBC_CarrDimension Carrier Dimension View

@AbapCatalog.sqlViewName: 'ZCARRDIM'
@Analytics: {dataCategory: #DIMENSION, dataExtraction.enabled: true} 
@ObjectModel.representativeKey: 'Carrid'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Carrier'
define view ZSAPBC_CarrDimension as select from ZSAPBC_Carr
 association [0..1] to ZSAPBC_CarrText as _Text on $projection.CarrID = _Text.CarrId
 {
  @ObjectModel.text.association: '_Text' 
  key CarrID,
  Url,
  CurrCode,
  @EndUserText.label: 'Region'
  Region,
  _Text
}

ZSAPBC_RegionHierarchy Region Hierarchy View

@AbapCatalog.sqlViewName: 'ZREGIONHIER'
@Analytics: { dataCategory: #HIERARCHY, dataExtraction.enabled: true }
@ObjectModel.representativeKey: 'Region'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Region'
@Hierarchy.parentChild.name: 'REGION_GEO'
@Hierarchy.parentChild.label: 'Region Geography'
@Hierarchy.parentChild: 
{ recurse:          {   parent: 'ParentNode',   child:  'HierarchyNode'   } }
define view ZSAPBC_RegionHierarchy as select distinct from ZSAPBC_Region 
 association[0..1] to ZSAPBC_RegionDimension as _Dimension on $projection.HierarchyNode = _Dimension.Region
{
  @ObjectModel.foreignKey.association: '_Dimension'
  key Region as HierarchyNode,
  MainRegion as ParentNode,
  _Dimension
}

ZSAPBC_RegionDimension Region Dimension View

@AbapCatalog.sqlViewName: 'ZREGIONDIM'
@Analytics: { dataCategory: #DIMENSION, dataExtraction.enabled: true }
@ObjectModel.representativeKey: 'Region'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Region'
define view ZSAPBC_RegionDimension as select from ZSAPBC_Region
association[0..*] to ZSAPBC_RegionHierarchy as _Hierarchy on $projection.Region = _Hierarchy.HierarchyNode
{
  @ObjectModel.Hierarchy.association: '_Hierarchy' 
  key Region,
  @EndUserText.label: 'Main Region'
  MainRegion,
  _Hierarchy
}

ZSAPBC_FlightFact Flight Fact Table View

@AbapCatalog.sqlViewName: 'ZSFLIGHTFACT'
@AbapCatalog.compiler.compareFilter: true
@Analytics.dataCategory: #FACT
@AccessControl.authorizationCheck:#NOT_REQUIRED
@EndUserText.label: 'Flight'
define view ZSAPBC_FlightFact with parameters
    @Consumption.defaultValue: 'CAD' 
    P_DisplayCurrency : s_currcode  
    as select from sflight inner join ZSAPBC_Carr as scarr 
                                   on sflight.carrid = scarr.CarrID {
  key sflight.carrid as CarrId,
  key sflight.connid as ConnId,
  key sflight.fldate as FlightDate,
  scarr.Region as Region,  
  $parameters.P_DisplayCurrency as Currency,
  currency_conversion( 
    amount             => sflight.paymentsum,
    source_currency    => sflight.currency,
    target_currency    => $parameters.P_DisplayCurrency,
    exchange_rate_date => sflight.fldate,
    exchange_rate_type => 'M',
    error_handling     => 'SET_TO_NULL'              // otherwise data inconsistencies cause a dump     
  ) as Payment,
  cast('EA' as abap.unit(3) ) as Unit,
  seatsmax + seatsmax_b + seatsmax_f as SeatsMax,
  seatsocc + seatsocc_b + seatsocc_f as SeatsOcc    
}

ZSAPBC_FlightCube Flight Cube View

@AbapCatalog.sqlViewName: 'ZSFLIGHTCUBE'
@AbapCatalog.compiler.compareFilter: true
@Analytics: { dataCategory: #CUBE, dataExtraction.enabled: true }
@AccessControl.authorizationCheck:#CHECK 
@EndUserText.label: 'Flight Cube'

define view ZSAPBC_FlightCube with parameters
  P_DisplayCurrency : s_currcode, 
  P_StartDate: dats,
  P_EndDate: dats       
  as select from ZSAPBC_FlightFact( P_DisplayCurrency:  $parameters.P_DisplayCurrency ) as flight inner join I_CalendarDate as Calendar on flight.FlightDate = Calendar.CalendarDate 
    association [1..1] to ZSAPBC_CarrDimension as _Carr on  $projection.CarrId  = _Carr.CarrID
    association [1..1] to ZSAPBC_RegionDimension as _Region on  $projection.Region  = _Region.Region
{
  key 'CURR' as PeriodType,
  @ObjectModel.foreignKey.association: '_Carr'  
  key flight.CarrId,
  key flight.ConnId,
  @EndUserText.label: 'Date'  
  key Calendar.CalendarDate as FlightDate,
  @EndUserText.label: 'Region'
  @ObjectModel.foreignKey.association: '_Region'
  flight.Region,
  @Semantics.calendar.yearWeek: true
  @EndUserText.label: 'Week'  
  Calendar.YearWeek as FlightWeek,  
  @Semantics.calendar.yearMonth: true
  @EndUserText.label: 'Month'  
  Calendar.YearMonth as FlightMonth,
  @Semantics.calendar.year: true
  @EndUserText.label: 'Year'
  Calendar.CalendarYear as  FlightYear,
  @EndUserText.label: 'Currency'  
  Currency,
  @Semantics.unitOfMeasure: true  
  @EndUserText.label: 'UOM'  
  flight.Unit,
  @Semantics.unitOfMeasure: true
  @EndUserText.label: '%'  
  cast( 'Z%' as abap.unit( 3 ) ) as PercentUnitOfMeasure,    
  @Semantics.amount.currencyCode: 'currency'
  @EndUserText.label: 'Payment'
  @DefaultAggregation: #SUM
  flight.Payment,
  @Semantics.quantity.unitOfMeasure: 'unit'
  @DefaultAggregation: #SUM  
  @EndUserText.label: 'Seats Max'
  SeatsMax,
  @Semantics.quantity.unitOfMeasure: 'unit' 
  @DefaultAggregation: #SUM  
  @EndUserText.label: 'Seats Occ'
  SeatsOcc,
  @Semantics.amount.currencyCode: 'currency'
  @EndUserText.label: 'Payment Ref'
  @DefaultAggregation: #SUM
  cast(0 as s_sum) as payment_ref,
  @Semantics.quantity.unitOfMeasure: 'unit'
  @DefaultAggregation: #SUM  
  @EndUserText.label: 'Seats Max Ref'
  cast(0 as s_seatsmax) as SeatsMaxRef,
  @Semantics.quantity.unitOfMeasure: 'unit'
  @DefaultAggregation: #SUM  
  @EndUserText.label: 'Seats Occ Ref' 
  cast(0 as s_seatsocc) as SeatsOccRef,
  @Semantics.quantity.unitOfMeasure: 'PercentUnitOfMeasure'
  @DefaultAggregation: #MAX
  @EndUserText.label: '100%'
  100 as HundredPercent, 
  _Carr,  
  _Region
 }
 where flight.FlightDate between $parameters.P_StartDate and $parameters.P_EndDate 
 
 
 union all
 
  select from ZSAPBC_FlightFact( P_DisplayCurrency:  $parameters.P_DisplayCurrency ) as flight
    inner join ZI_CalendarDate as Calendar on flight.FlightDate = Calendar.CalendarDateMinus1Year 
    association [1..1] to ZSAPBC_CarrDimension as _Carr on  $projection.CarrId  = _Carr.CarrID
    association [1..1] to ZSAPBC_RegionDimension as _Region on  $projection.Region  = _Region.Region
{
  key 'PREV' as PeriodType,
  @ObjectModel.foreignKey.association: '_Carr'  
  key flight.CarrId,
  key flight.ConnId,
  @EndUserText.label: 'Date'  
  key Calendar.CalendarDate as FlightDate,
  @EndUserText.label: 'Region'
  @ObjectModel.foreignKey.association: '_Region'
  flight.Region,
  @Semantics.calendar.yearWeek: true
  @EndUserText.label: 'Week'  
  Calendar.YearWeek as FlightWeek,  
  @Semantics.calendar.yearMonth: true
  @EndUserText.label: 'Month'  
  Calendar.YearMonth as FlightMonth,
  @Semantics.calendar.year: true
  @EndUserText.label: 'Year'
  Calendar.CalendarYear as  FlightYear,
  @EndUserText.label: 'Currency'  
  Currency,
  @Semantics.unitOfMeasure: true  
  @EndUserText.label: 'UOM'  
  flight.Unit,
  @Semantics.unitOfMeasure: true
  @EndUserText.label: '%'  
  cast( 'Z%' as abap.unit( 3 ) ) as PercentUnitOfMeasure,
  @Semantics.amount.currencyCode: 'currency'
  @EndUserText.label: 'Payment'
  @DefaultAggregation: #SUM
  cast(0 as s_sum) as payment,
  @Semantics.quantity.unitOfMeasure: 'unit'
  @DefaultAggregation: #SUM  
  @EndUserText.label: 'Seats Max'
  cast(0 as s_seatsmax) as SeatsMax,
  @Semantics.quantity.unitOfMeasure: 'unit'
  @DefaultAggregation: #SUM  
  @EndUserText.label: 'Seats Occ'
  cast(0 as s_seatsocc) as SeatsOcc,
  @Semantics.amount.currencyCode: 'currency'
  @EndUserText.label: 'Payment Ref'
  @DefaultAggregation: #SUM
  flight.Payment as payment_ref,
  @Semantics.quantity.unitOfMeasure: 'unit'
  @DefaultAggregation: #SUM  
  @EndUserText.label: 'Seats Max Ref'
  flight.SeatsMax as SeatsMaxRef,
  @Semantics.quantity.unitOfMeasure: 'unit'
  @DefaultAggregation: #SUM  
  @EndUserText.label: 'Seats Occ Ref'
  flight.SeatsOcc as SeatsOccRef,
  @Semantics.quantity.unitOfMeasure: 'PercentUnitOfMeasure'
  @DefaultAggregation: #MAX
  @EndUserText.label: '100%'
  100 as HundredPercent,
  _Carr,  
  _Region
 }
 where Calendar.CalendarDate between $parameters.P_StartDate and $parameters.P_EndDate

Note:

Reporting period data is union with reference period data;
Extended I_CalendarDate CDS View is used to offset reporting period date range by 365 or 366 days and such a way get reference period date range

ZSAPBC_FlightQuery Flight Query View

@AbapCatalog.sqlViewName: 'ZFLIGHTQUERY'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck:#CHECK
@VDM.viewType: #CONSUMPTION
@Analytics.query: true 
@OData.publish: true 
@EndUserText.label: 'Flight Query'
define view ZSAPBC_FlightQuery with parameters
  @EndUserText.label: 'Display Currency'     
  @Consumption.defaultValue: 'CAD'
  P_DisplayCurrency : s_currcode,
  @EndUserText.label: 'Date Function'                               
  @Consumption: { valueHelp: '_DateFunction.DateFunction', defaultValue: 'YEARTODATE' } 
  P_DateFunction : datefunctionid,
  @Consumption.derivation: { lookupEntity: 'C_SglGregorianCalDateFunction', 
  resultElement: 'DateFunctionStartDate', binding: [ {
    targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' },
     { targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE'  } ] 
  } 
  @Consumption.hidden: true
  P_StartDate: /srmsmc/puc_spend_valid_from ,
  @Consumption.derivation: { lookupEntity: 'C_SglGregorianCalDateFunction', 
  resultElement: 'DateFunctionEndDate', binding: [ {
    targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' },
     { targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE'  } ] 
  } 
  @Consumption.hidden: true 
    P_EndDate: /srmsmc/puc_spend_valid_to     
     as select from ZSAPBC_FlightCube( P_DisplayCurrency:  $parameters.P_DisplayCurrency, 
                                       P_StartDate: $parameters.P_StartDate,
                                       P_EndDate: $parameters.P_EndDate ) 
  association[1] to ZI_DateFunction as _DateFunction on _DateFunction.DateFunction <> ''  
{
  Region,
  @AnalyticsDetails.query.axis: #FREE    
  ConnId,          
  @AnalyticsDetails.query.axis: #FREE
  @Consumption.filter: {selectionType: #SINGLE, multipleSelections: true, mandatory: false }
  CarrId,
  @AnalyticsDetails.query.axis: #FREE
  FlightDate,
  @AnalyticsDetails.query.axis: #FREE
  FlightWeek,  
  @AnalyticsDetails.query.axis: #FREE
  FlightMonth,
  @AnalyticsDetails.query.axis: #FREE
  FlightYear,    
  @AnalyticsDetails.query.axis: #FREE
  Currency,
  @AnalyticsDetails.query.axis: #FREE
  Unit,    
  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.hidden: true    
  Payment,    
  @AnalyticsDetails.query.axis: #FREE 
  @AnalyticsDetails.query.hidden: true    
  SeatsMax,
  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.hidden: true    
  SeatsOcc,
  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.hidden: true    
  payment_ref,
  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.hidden: true    
  SeatsMaxRef,
  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.hidden: true    
  SeatsOccRef, 
  @AnalyticsDetails.query.axis: #COLUMNS
  @AnalyticsDetails.query.decimals: 1
  @EndUserText.label: 'Seats Occ Rate'
  @AnalyticsDetails.query.formula: 'NDIV0( NODIM( SeatsOcc ) / NODIM( SeatsMax ) ) * HundredPercent' 
  0 as SeatsOccRate,
  @AnalyticsDetails.query.axis: #COLUMNS
  @AnalyticsDetails.query.decimals: 1
  @EndUserText.label: 'Seats Occ Rate Ref'
  @AnalyticsDetails.query.formula: 'NDIV0( NODIM( SeatsOccRef ) / NODIM( SeatsMaxRef ) ) * HundredPercent'
  0 as SeatsOccRateRef,    
  _DateFunction
}

Note:

ZI_DateFunction value help is assigned P_DateFunction parameter for ease of user
Reporting period is selected by name (P_DateFunction parameter) which is converted to date range selection by C_SglGregorianCalDateFunction view

1 Comment

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

Michelle Crapo

June 28, 2019 at 4:53 pm

Nice examples! I need them. The code snippets are awesome. I wonder how I get my higher ups to want the visuals. Or I just do it for fun.

Technical Articles

Comparative Analysis with S/4 HANA Embedded Analytics

Assigned Tags