Why quantities in Charts in SAP IBP Excel Add-In are sometimes divided by 1000, rounded, + suffix K

[vincentverheyen]

Sharing this Q&A for the interest of the community:

Why, in the chart of the SAP IBP Excel Add-In, do we sometimes (in certain charts / templates / planning views) see a value such as 1582 displayed in full as: 1,582; whereas at other times we see it divided by 1000, rounded, and with a suffix K, displayed as: 2K

[vincentverheyen · Accepted solution]

The reason is that, the chart uses the chart functionality of MS Excel. And there are many settings one can choose in these charts.

In this sense, you will see a quantity 1,582 displayed in full as 1,582 in the chart of Best Practices template DP 140 Global Demand Plan:

However, if we look at the same values, on the same aggregation level (time and Master Data attributes), in the chart of Best Practices template  DP 110 Historical Data Cleanse:

Compare the values above the column charts in the 2 screenshots above.

The reason of the difference is not a difference in the width of the columns in our Excel Sheets, nor is it because of any settings in 'Edit View' > 'View Formats' (which opens the hidden sheet 'IBPFormattingSheet'). Instead, the reason is to be found within the options of the charts in either templates. The relevant 'Chart Design' options can be easily inspected and/or changed, by clicking on the numbers in the chart, selecting them as such:

Subsequently right-click, and choose 'Format Data Label...':

In the first case, we note that the 'Number' > Category' is defined as 'Number' in the following manner:

But if we execute the same process for the second template, we see a different setting:

Right-click on the selected Data Label, and choose 'Format Data Label...':

We notice that the 'Number' > Category' is defined not as 'Number' here but instead as 'Custom' and in the following manner:

[>=1000000]0,,"M";[>=1000]0,"K";#""

And this is the reason for the difference. This is a custom number format used in Excel to display large numbers in a more readable way. It can be understood as such:

• [>=1000000]0,,"M": If the number is greater than or equal to 1,000,000, it will be displayed in millions (M). For example, 2,500,000 will be shown as 2.5M.
• [>=1000]0,"K": If the number is greater than or equal to 1,000 but less than 1,000,000, it will be displayed in thousands (K). For example, 1,582 will be shown 2K since the number would be divided by 1,000 and rounded to the nearest whole number.
• #: This is the default format for numbers that don't meet the previous conditions. It will display the number as is.

I posted this Q&A to be of help to the wider SAP IBP the community. Note that, once understood that the SAP IBP Excel Add-In Chart is using the functionalities of the Microsoft Excel 'Chart Design', one can start to experiment with a whole variety of different chart types, for example by right-clicking on the chart and choosing 'Change Series Chart Type...'.