Dates Matter: Mapping Data Using the Custom Date Function in Anaplan
Let’s Talk Time
To harness the full power of Anaplan and utilize the full scope of its planning capabilities, it is best practice to implement its built-in Time Dimension. A common issue we have encountered is data that is governed by a textual date, but not mapped to the Time Dimension. By not using the Time Dimension and inputting dates as text, the ability to perform calculation functions that rely on Anaplan’s Time Dimension is restricted. For example, modules would not be able to make use of Anaplan’s logic functions such as END or START. By restricting the functionality that can be used in a module, logic can become very complicated and difficult to execute.
Additionally, it is common for these dates to be stored using an unconventional date format. This can further complicate the process or discourage users from properly mapping data to begin with.
To solve this problem and encourage use of the Time Dimension when building models, we’ll walk through a simple example that utilizes an unconventional date format and map it to the target module.
This example will consist of 2 lists (both flat, simple lists) and 2 modules. The first module will contain the raw data and the second will be the target module we will map to. The lists and modules are outlined below.
In our example we will use an unconventional date format which will appear as follows:
To import the data, a new module is created. The new module is then fed data from the “source” module (in this case, the module utilizing the unconventional date format). In the new module, the following is performed:
Data > Import > Connect to Anaplan Model > Custom Date Mapping Example (Module Name) > Transactions Module (Data we wish to import)
*Please note this data can also be loaded via a flat .csv file using the same methodology discussed going forward.
Once the import path is selected, a prompt is given to begin the module mapping process. The following selections are made:
Time: Column 4
Transactions: Column 1
Sales Team: Salesperson
Line Items: Column Headers
Next, choose the time tab. Before moving forward, it is important to understand the difference between Dates, Periods, and Periods in a Year.
Dates refer to data that contains a day, month, and year regardless of how it is written. Some examples of a date include:
Jan 01 17
In both cases there is a day, a month, and a year
Periods refer to data that contains a month and year but no day of the month. For example:
Both conventions use a month and a year format
Finally, Periods in a year refer to data that is simply a date written as a word or numerical period. For example:
In this case the data would only refer to a month
In the example data the Date of Transactions column is formatted using an unconventional format, PERXX-DAYXX-YRXX. As explained above this would be considered a date as it uses a day, month, and year. In order to map it we will use Anaplan’s custom date capabilities by selecting Date from the time menu and inputting a Custom Fixed Position Pattern at the bottom of the date time tab.
Select Dates from “How to map the source data to the timescale” menu
Input mapping format into custom fixed – position pattern where question marks (?) are substituted for any value that is not a day, month, or year and DDMMYY format is used where the day, month or year exist in the text string. This results in the following input ???MM????DD???YY
Run the Import
Once the import is executed the data will successfully map to the date dimension as seen below.
To see the final result in its entirety, the data mapping module has been pivoted so that all transactions can be seen from a total company perspective. As seen below, all transactional data has been mapped to its appropriate period.
With the transactional data mapped to the appropriate time period, users can move forward and conduct additional analysis using the complete tool set available in the Anaplan platform!
For assistance with any of Anaplan’s powerful capabilities or for a demo, please reach out to us!