By implementing boolean logic, we can define a condition where the result is either true or false. We can then perform an action based on whether the result is true or false. This is a useful technique used in formulas.
For boolean operations, the CASE - WHEN - THEN statement is used. Only measures, restricted measures or calculated measures are allowed in this statement.
Let's look at the code that defines a boolean operation:
123456789101112131415161718
@AnalyticsDetails.query.axis: #COLUMNS
MaximumSeats,
@AnalyticsDetails.query.axis: #COLUMNS
OccupiedSeats,
abap.unit'%' as Percent,
@Aggregation.default: #FORMULA
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: 'Occupation Rate'
@Semantics.quantity.unitOfMeasure: 'Percent'
ratio_of( portion => OccupiedSeats,
total => MaximumSeats ) *100 as OccupationRate,
@Aggregation.default: #FORMULA
@EndUserText.label: 'Number of poor utilized Flights'
@AnalyticsDetails.query.axis: #COLUMNS
case when $projection.OccupationRate <= abap.decfloat34'66' then NumberOfFlights else 0 end as NumberOfPoorUtilizedFlights,
NumberOfFlights
For our analytical query, we have defined five measures. Pay special attention to the measure Number of poor utilized Flights which is based on a boolean operation. Here we count the number of flights where the Occupation Rate is less than or equal to 66% which is the threshold that we have chosen to define a poorly utilized flight.
Notice that airline AZ has two flight numbers, AZ/788 andAZ/789. For each of these flight numbers, there are 14 flights in the result (let's imagine there is one flight per day and our analytical query result is restricted to two weeks). We can see from the column Number of poor utilized Flights that flight number AZ/789 is poorly utilized with 14 flights.
But what does the value 14 represent?
Firstly, remember that, by default, a calculation is always performed on the aggregated result of the dimensions. So in our case, the boolean logic is applied on the aggregated result of the combination of airline and flight. Notice the occupation rate for airline AZ and flight 789 is 63%. This means that this flight falls into the trueside of the boolean condition as it is less that 66% . So in this case, the column Number of poor utilized Flights is defined using the analytical query measure NumberOfFlights, which is 14. The column label Number of poor utilized Flights is misleading because it appears that every one of the 14 flights is poorly utilized. But this is not the case, and in fact only 7 of the 14 flights for AZ/789 are poorly utilized. But how would we discover that? We would need to add a drill-down by flight date so this would become clear.
Watch the video below to learn how we implement boolean logic within a formula.
We saw how the count of poorly utilized flights is executed at the aggregated level of airline / flight number. This is not incorrect, but based on the label of the column Number of Poor Utilized Flights this is misleading. The business user would expect to see the true number of flights and might not know to drill down the flight date to reveal this information. What we should do is adjust the calculation so that it executes at the more granular level of airline / flight / flight date. To do this we must implement an exception aggregation which forces the calculation to execute at a specific level of aggregation, which might not be the level of aggregation that is displayed in the analytical query result. We will cover exception aggregation in the next section.
