-
Notifications
You must be signed in to change notification settings - Fork 4
1. Calculated & Restricted Key Figures
The query /DMO/ANA_C_RKF_Simple shows a comparison of a measure for different month. The feature behind is called restricted measure and in CDS the pattern is CASE WHEN THEN ELSE NULL END AS
Note that, since different currencies are involved, the totals cannot be calculated (hence * in the preview). The measure can be used together with one of the functions CURR_TO_DECFLOAT_AMOUNT, CURRENCY_CONVERSION, UNIT_CONVERSION
The query /DMO/ANA_C_RKF_WithCurrConv shows a measure of type amount with currency conversion (see field SalesAmountInAugInEUR). For simplicity all the parameters of the currency conversion are static. Parameters or session variables (e.g. $Session.user_date) can be used.
Note that if the conversion isn't performed in your system, then make sure that the conversion rates are available for 08-August-2024 or choose another exchange rate date. See how to import conversion rates in this GitHubs README.
The query /DMO/ANA_C_RKF_WITHFUNCTION shows how to use functions in restricted measures. Allowed functions for a filter in a restricted measure on the right side are:
- CALENDAR_SHIFT,
- CALENDAR_OPERATION,
- FISCAL_CALENDAR_SHIFT,
- FISCAL_CALENDAR_OPERATION,
- DATS_ADD_DAYS, DATN_ADD_DAYS Note that the element labels are derived dynamically at runtime
See also CDS View Time Comparison.
The query /DMO/ANA_C_RKF_IGNOREFILTER is introducing the concept of filters. This query shows restricted measures: OccupiedSeatsAll, OccupiedSeatsConnection The feature to ignore filters is usefull, if you want to compare the number to a bigger group using a formula With the drill down of AirlineID and DepartureAirportID, OccupiedSeats shows the number of occupied seats per airline and departure airport OccupiedSeatsConnection shows the number of occupied seats per airline
See also How to use the analyticsdetails.query.ignorefurtherfilter annotation.
The query /DMO/ANA_C_CKF_SIMPLE shows how formulas on measures are handled. Note that the data is first aggreated and afterward the formula is calculated on the aggregated operands. This is special for the analytical query. In plain SQL the formula is calculated on row level and then aggregated. What it means can be observed best for fields "OccupationRate" and "FreeSeatsRate".
The query /DMO/ANA_C_CKF_Amount addresses how to work with Currencies and Amounts. In SAP systems amounts are stored with 2 decimals if the field of type CURR. This is independent of the true number of decimals of the currency. The function CURR_TO_DECFLOAT_AMOUNT casts the value from CURR to DECFLOAT and does the correct decimal shift. The function is needed when CURR fields should be used in formulas. But it is not allowed at all places in a formula. Therefore the recommandation is to apply the function for all CURR-fields from the query. There are some exceptions, like get_numeric_value, .... In this query CURR_TO_DECFLOAT_AMOUNT is applied to field "SalesAmount", such that it can be generally used in formulas.
The query /DMO/ANA_C_CKF_CURRCONV shows how to define a currency conversion - see field "SalesAmountInEUR" To keep the example simple constants are used for the conversion parameters. Usually parameters or session variables are used for these parameters.
The query /DMO/ANA_C_CKF_PERCENT shows how to deal with percentage numbers. When the result of a calculation is a number without reference, then the Semantics annotation can be added which points to an unit-field which is set to "%". Note that the result of deviation_ratio is of type DECFLOAT. The number on the screen will show "50 %".
The query /DMO/ANA_C_CKF_BOOLEAN shows how to define a formula with a condition. See field "NumberOfPoorUtilizedFlights". Note that the Condition is calculated after aggregation. This means that the label '# poorly occupied flights' is not 100% correct. More precisely the field shows the number of flights in case the occupation rate on aggregated level is poor. If you want the number of poorly occupied flights, you have to use the exception aggregation (see for example query /DMO/ANA_C_CKF_EXCAGGR. Furthermore, field "OccupationRate" shows how to deal with percentage numbers
The query /DMO/ANA_C_CKF_EXCAGGR_CNT shows the feature of "exception aggregation" - see field "NumberOfAirplains". It is useful if something should be counted. "Exception aggregation" means that logically, data is read on the level of the exception aggregation elements (in this case PlaneType) plus the elements on rows and columns in the query result. On this level the formula is calculated (in this case the result is 1) and then the data is aggregated with exception aggregation behavior (here SUM) to the level of the query result. With this you count the number of distinct "PlaneTypes".
The query /DMO/ANA_C_CKF_EXCAGGR shows the feature of "exception aggregation" - see field "CorrectNumber". Field "NumberOfPoorUtilizedFlights" is based on the same formula but it is calculated without exception aggregation. "Exception aggregation" means that logically, data is read on the level of the exception aggregation elements (in this case AirlineID, ConnectionID and FlightDate) plus the elements on rows and columns in the query result. On this level the formula is calculated and then the data is aggregated with exception aggregation behavior (here SUM) to the level of the query result.