SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

Top 10

Challenge

SAC table widget offers the possibility to show top N (e.g. top 10) based on key figure value (e.g. Quantity sold). After applying this option, N (e.g. 10) members of selected dimension will be listed. However, if there is the requirement that the remaining members which are excluded from the top N table, should be summed up to one row below the top N members, we could not produce this view with standard SAC table.
 

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

Solution

With the embedded of R visualization in SAC, we could leverage the R Scripting to satisfy this demand easily. Within this insight, we use the case study of Best Run to show top N Sales Managers in terms of Quantity Sold. We will also explain the step to integrate the input control to select different value of Top members (eg. Top 5, Top 10, Top 15) via Input Paramters and the scripts to get the percentage column next to the keyfigure column (Quantity sold in this case). One of the interesting possibilities of using R within SAC story is that it’s not a standalone widget but it could be interacted with Story filter or Input control of the page. As so, the R widget is not disconnected to the analysis report environment.

SAC Story and R Visualization

Create a story based on the Best Run model and put a R Visualisation in the canvas. We could add input control e.g Location, Product if desired.

R packages which are necessary to form the visualization as required are: dplyr, flextable and officer.

For the input data, select the model and choose Sales Manager in rows, Account: Quantity Sold in Column. 

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

For Input Parameters

Create Static List of value 5; 10; 15 and give name “TopN”.

This input control could be used as a variable in R script later.

 

Add the script to the R visualisation by opening the script window and add the following script (shown below)

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

Code and explanation

Create data frame from Model input including Sales Manager and Quantity Sold

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

Sort Quantity Sold and get the Top members based on TopN parameter. If the number of Sales Manager is more than parameter value, the data frame will include the line Others as the sum of the remaining. “Totals” row also be added.

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

This line calculates percentage by dividing Quantity Sold to Total Quantity Sold.

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

This part of code aims at formatting decimal place and to use “,” for decimal.

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

Finally, we could display the data as a table by using package flextable.

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

 

Result

 

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

SAP Analytics Cloud Tips - Show remaining sum in a Top N table with R visualization

SAP Analytics Cloud version used in this article:
System Name 6EA70
Version 2021.1.1
Build Date Wed Dec 09 2020 19:27:14+0100

Keen to know more?

Continue reading or contact us to get started:

Competence