Author: Andrew Barnett is a Certified Master Anaplanner and Vice President at PJT Partners.
Today, we will be discussing a scenario that we often run into in Anaplan. Numeric data comes in with units of measure, whether explicitly stated or implied in the data. The data might be a currency, like US Dollars or Mexican Pesos; it might be a percentage; it could be a count like packages; or maybe something else like weights. What is the best approach for dealing with this? When is it best to use alphanumeric imports for numbers? It is possible to import "mixed" format values into numeric cells (e.g. 15mg) but when does it make sense to do this?
From a pure display perspective, I’ll give a brief overview but for a more extensive overview of the numerous ways you can format numbers in Anaplan, I recommend an excellent blog I saw recently, here:
https://seymatash.medium.com/mastering-number-formats-in-anaplan-6f1aa8eb0399
Outside of showing the final output as a text field, which allows for full customization but has performance impacts and is not recommended for this and other reasons, Anaplan number formatting within a model is quite customizable.
It’s important to note that the UX has the ability to override the format of a number formatted line item, regardless of what was previously set before on the line item. This is not just in grids but now within charts as well.
Within a number formatted line item, there are a number of units, we’ll start with Currency, which shows the symbol or the currency code.
Percentages are the most straightforward. 95+% of the time in Anaplan, users just click percentage. The rest of the time, users generally just leave units set to None.
For currencies, there are a few different ways to handle it. If the particular line item always has a specific currency attached to it, users generally select their currency, select the symbol or currency code they wish to display for the currency (or leave units set to None). However, if the currency can vary, generally the units are split into a separate line item, which is tied to a Currency list and an exchange rate.
The primary reason for this blog is to discuss something less often seen, Custom Units. To show an example of how loading this data looks in practice, note this sample data file with data laid out in a few different ways:
Here’s the output of it being loaded into Anaplan as well as how one of the line items was formatted:
If the line item in Anaplan is number formatted, Anaplan imports the numbers from Excel correctly, regardless of whether there was a prefix or a suffix in the data. However, if the source data has a mixture of different prefixes/suffixes, but the number is formatted with a specific Custom Unit, Anaplan just takes the number from the data and pairs it with the Custom Unit selected in the line item. Because of this, it’s extremely important to not use Custom Units for any line item where the unit of measure in the source data may be mixed.
At this point, you might be wondering why someone would ever use custom units. The first reason is because numeric data should be imported as number format wherever possible. Rob Marshall found that data formatted as text takes up many times more memory than the same data formatted as number (screenshot below). However, this only works when the units of measure are consistent in the data loaded to a specific line item. If they are not, the data needs to be separated, then converted into a standard unit of measure, for aggregation and reporting.
It’s best to do as much of this splitting and transformation before loading into Anaplan, or at least, in ADO or the Data Hub before pulling into a planning model.
In summary, our rule of thumb is to import the data as number format with Custom Units set when the following conditions are met:
- The unit of measure is consistent for all data that will be imported in that line item.
- Those using the data need to see the unit of measure in a generally consistent manner throughout the model.
- The team intends on doing some sort of calculation/aggregation with this number.
Lastly, one should always adhere to Planual rule 5.04-07: Import using the correct formats. In this context, it means you should import numeric data as a number where possible.
Questions? Leave a comment!
