Using Boolean Operators
Exercise 4: Create Calculated Key Figures
Exercise Objectives
After completing this exercise, you will be able to:
• Define new calculated key figures at the InfoProvider and query level, and include them in a query definition
• Define new calculated key figures using Boolean operators
Business Example
You need additional calculations for the analysis of sales volumes in order to carry out a comparison between the sales volumes of two different time periods. You want to show the difference, the percentage variance of the sales volumes, and the share of sales volumes for each sold-to party in relation to the total sales volume of the time period in question.
Task 1:
You should create a report that allows you to compare the sales volumes of your individual sold-to parties of sales organization 1000 within two time periods.
Define three new calculated key figures or new formulas that list the difference and percentage variation of the sales volumes of November and December 2006.
You also need information on the percentage share of the sales volume of a sold-to party in relation to the total sales volume for December 2006. You should use an additional column to display the subtotals of the sales volumes for December, so that you can hide the subtotal rows in your report.
1. Create a new query using the InfoProvider MultiProvider -Customer Sales (P_SALES01) in the Reporting Layer → Sales and Distribution InfoArea with the characteristic Sold to party (T_SOLD_TO) in the rows.
Use the Division (0DIVISION) as a free characteristic. Add Sales organization (0SALESORG) to the Filter area as a Characteristic Restriction and filter according to Germany Frankfurt (1000).
In your query, use the two restricted key figures that have already been defined as Sales Volume EUR 11/2006 (P_RKF_SV_11_2006) and Sales Volume EUR 12/2006 (P_RKF_SV_12_2006) in the columns.
Define a new Calculated Key Figure at the InfoProvider level:
Description: U## Sales Volume Diff. 12-11/2006
Technical name: U##_DIFF001
This is calculated as the difference of the two restricted key figures that have already been defined: Sales Volume EUR 11/2006 (P_RKF_SV_11_2006) and Sales Volume EUR 12/2006 (P_RKF_SV_12_2006). When finished, place the new key figure into the columns.
2. In the same query, define a second Calculated Key Figure at InfoProvider level.
Description: U## Sales Volume EUR % Var. 11/12/2006 Technical name: U##_CKF_PERC001
This key figure is for calculating the percentage variance of the sales volumes for the months November and December 2006. Once you have defined this key figure, place it in the Columns area of the query definition.
3. Now define a new Formula, this time at Query level.
Description: % Share of overall result Sales Volume EUR 12/2006
This calculates, for the month of December, the percentage share of sales volume for a sold to party in relation to the overall result. Place the new key formula as the last element in the key figure structure.
4. To display the subtotal of the sales volumes for December 2006 in a separate column, define a new formula in the same query. Position it as the last key figure in the structure.
Description: Result of Sales Volume EUR 12/2006 5. Save the query role Roles → Role## → Queries under:
Description: U## Query Calc. Keyf. 001 Technical name: U##_QD_CKF001
Now execute the query.
Add the Division to your report as the first column in the drilldown, and evaluate the behavior of the last two formulas in the report.
Task 2:
You have calculated the percentage variance of the sales volumes achieved in the months November and December 2006. You give a staggered bonus for an increase in sales volume for each sold-to party.
1. In the query definition U## Query Calc. Keyf. 001(U##_QD_CKF001) define a new formula called Bonus that calculates a bonus of 5% for percentage sales volume increases of more than 10%, and a bonus of 1% for percentage increases between 0 and 10%, based on Sales Volume EUR 12/2006.
2. For sold-to parties who achieved no sales volume in November 2006, the error message DIV/0 appears when the bonus and percentage variance are calculated.
However, you want this sold-to party to be included in bonus calculation with a bonus of 1%. Therefore, you use the data function NDIV0(x) on the calculated key figure U## Sales Volume EUR % Var. 11/12/2006 (U##_CKF_PERC001) to receive the value zero instead of an error message. This value allows the bonus calculation to be carried out appropriately.
Solution 4: Create Calculated Key Figures
Task 1:
You should create a report that allows you to compare the sales volumes of your individual sold-to parties of sales organization 1000 within two time periods.
Define three new calculated key figures or new formulas that list the difference and percentage variation of the sales volumes of November and December 2006.
You also need information on the percentage share of the sales volume of a sold-to party in relation to the total sales volume for December 2006. You should use an additional column to display the subtotals of the sales volumes for December, so that you can hide the subtotal rows in your report.
1. Create a new query using the InfoProvider MultiProvider -Customer Sales (P_SALES01) in the Reporting Layer → Sales and Distribution InfoArea with the characteristic Sold to party (T_SOLD_TO) in the rows.
Use the Division (0DIVISION) as a free characteristic. Add Sales organization (0SALESORG) to the Filter area as a Characteristic Restriction and filter according to Germany Frankfurt (1000).
In your query, use the two restricted key figures that have already been defined as Sales Volume EUR 11/2006 (P_RKF_SV_11_2006) and Sales Volume EUR 12/2006 (P_RKF_SV_12_2006) in the columns.
Define a new Calculated Key Figure at the InfoProvider level:
Description: U## Sales Volume Diff. 12-11/2006 Technical name: U##_DIFF001
This is calculated as the difference of the two restricted key figures that have already been defined: Sales Volume EUR 11/2006 (P_RKF_SV_11_2006) and Sales Volume EUR 12/2006 (P_RKF_SV_12_2006). When finished, place the new key figure into the columns.
a) To define a new query, open the Query Designer.
Choose New Query.
Select InfoProvider: InfoAreas → Reporting Layer → Sales and Distribution → MultiProvider - Customer Sales (P_SALES01).
Choose Open.
b) Place the Sold to party (T_SOLD_TO) characteristic found in the Customer dimension into the Rows section.
Place the Division (0DIVISION) characteristic found in the Sales area data dimension into the Free Characteristics section.
c) Place the Sales Organization (0SALESORG) characteristic found in the Sales area data dimension into the Characteristic Restriction section.
In the context menu for Sales Organization, choose Restrict → Single Values. Select Germany Frankfurt (1000) and adopt the selection using the Arrow button. Choose OK.
d) In the InfoProvider area, expand the Key Figures folder to show the Restricted Key Figures folder. From the Restricted Key Figures folder, place Sales Volume EUR 11/2006 (P_RKF_SV_11_2006) and Sales Volume EUR 12/2006 (P_RKF_SV_12_2006) into the Columns using Drag&Drop.
e) To define the first calculated key figure at InfoProvider level, choose New Calculated Key Figure in the context menu of the Key Figures folder. Then choose Edit in the context menu of the New Calculated Key Figure.
Description: U## Sales Volume EUR Diff. 12-11/2006 Technical name: U##_CKF_DIFF001
f) From the Available Operands window, drag Sales Volume EUR 12/2006 (P_RKF_SV_12_2006) from the Restricted Key Figures folder into the Detail View section using Drag&Drop. Choose Minus (-) and then place Sales Volume EUR 11/2006 (P_RKF_SV_11_2006) into the Detail View section using Drag&Drop. Choose OK.
g) To add the newly defined key figure to the query definition, use Drag&Drop to place U## Sales Volume Diff. 11/12 2006 into the Columns section of your query.
2. In the same query, define a second Calculated Key Figure at InfoProvider level.
Description: U## Sales Volume EUR % Var. 11/12/2006 Technical name: U##_CKF_PERC001
This key figure is for calculating the percentage variance of the sales volumes for the months November and December 2006. Once you have defined this key figure, place it in the Columns area of the query definition.
a) To define the second calculated key figure, again at InfoProvider level, choose New Calculated Key Figure in the context menu of the Key Figures folder. Then choose Edit in the context menu of the New Calculated Key Figure.
Description: U## Sales Volume EUR % Var. 11/12/2006. Technical name: U##_CKF_PERC001
b) Use Drag&Drop to place Sales Volume EUR 12/2006 (P_RKF_SV_12_2006), Operators → Percentage Functions
→ Percentage Variance (%) and Sales Volume EUR 11/2006 (P_RKF_SV_11_2006) into the Detail View section.
Choose OK.
c) Drag the new calculated key figure into the Columns section of your query.
3. Now define a new Formula, this time at Query level.
Description: % Share of overall result Sales Volume EUR 12/2006
This calculates, for the month of December, the percentage share of sales volume for a sold to party in relation to the overall result. Place the new key formula as the last element in the key figure structure.
a) For the definition of the new Formula, choose New Formula in the context menu for the Key Figures Structure. Then choose Edit in the context menu of the new Formula.
Description: % Share of overall result Sales Volume EUR 12/2006
Note: Use the Enter key for line breaks.
b) Use Drag&Drop to place the Percentage share of overall result (%GT) function found in Operators → Percentage Functions into the Detail View section. Then do the same with Sales Volume EUR 12/2006 (P_RKF_SV_12_2006) found in Available Operands.
Choose OK.
c) Drag the new formula to be the last element in the key figure structure.
4. To display the subtotal of the sales volumes for December 2006 in a separate column, define a new formula in the same query. Position it as the last key figure in the structure.
Description: Result of Sales Volume EUR 12/2006
a) For the definition of the new formula, choose New Formula in the context menu for the Key Figures structure. Then choose Edit in the context menu of the new Formula.
Description: Result of Sales Volume EUR 12/2006
b) Use Drag&Drop to place the Result (SUMCT) function found in Operators
→ Data Functions into the Detail View section. Then do the same with the structure element Sales Volume EUR 12/2006 (P_RKF_SV_12_2006) found in Available Operands.
Choose OK.
c) Drag the new formula to be the last key figure in the structure.
5. Save the query role Roles → Role## → Queries under:
Description: U## Query Calc. Keyf. 001 Technical name: U##_QD_CKF001
Now execute the query.
Add the Division to your report as the first column in the drilldown, and evaluate the behavior of the last two formulas in the report.
a) To save the query, choose Save Query.
Select Roles → Role## → Queries.
b) Enter:
Description: U## Query Calc. Keyf. 001 Technical name: U##_QD_CKF001
c) Execute the query.
d) Include the Division in the drill-down in front of the Sold to Party by using drag and drop navigation.
Task 2:
You have calculated the percentage variance of the sales volumes achieved in the months November and December 2006. You give a staggered bonus for an increase in sales volume for each sold-to party.
1. In the query definition U## Query Calc. Keyf. 001(U##_QD_CKF001) define a new formula called Bonus that calculates a bonus of 5% for percentage sales volume increases of more than 10%, and a bonus of 1% for percentage increases between 0 and 10%, based on Sales Volume EUR 12/2006.
a) Open the query definition.
In the context menu for the Key Figures Structure, choose New Formula.
Then choose Edit in the context menu of the new Formula.
b) Description: Bonus
c) Create the following formula in the Detail View section by using Boolean Operators :
(U## Sales Volume EUR % Var. 11/12/2006 > 10) * (Sales Volume EUR 12/2006 * 0.05) + (U## Sales Volume EUR % Var. 11/12/2006
>= 0 AND U## Sales Volume EUR % Var. 11/12/2006< = 10) * (Sales Volume EUR 12/2006 * 0.01)
d) Position the new formula as the last key figure in the structure.
e) Save the query and then execute it again to view the results.
2. For sold-to parties who achieved no sales volume in November 2006, the error message DIV/0 appears when the bonus and percentage variance are calculated.
However, you want this sold-to party to be included in bonus calculation with a bonus of 1%. Therefore, you use the data function NDIV0(x) on the calculated
key figure U## Sales Volume EUR % Var. 11/12/2006 (U##_CKF_PERC001) to receive the value zero instead of an error message. This value allows the bonus calculation to be carried out appropriately.
a) Open the query definition.
b) Locate (at the InfoProvider level) your calculated key figure U## Sales Volume EUR % Var. 11/12/2006 (U##_CKF_PERC001). Double-click the key figure to open the Formula Editor and change to the edit mode.
c) Position the cursor at the beginning of the formula. In the Data Functions folder, double-click the NDIV0(x) function to insert this function at the start of the formula.
d) Delete the close bracket. Then add the close bracket to the end of the formula.
e) Choose OK to close the formula editor.
f) Save the query, and execute it.
Lesson Summary
You should now be able to:
• Explain the objectives and use of calculated key figures in information analyses
• Define calculated key figures at the InfoProvider level and at the query level
• Define a calculated key figure and defining missing objects directly
• Using Boolean Operators within calculated key figures