Untangling Anaplan time mapping in imports

Misbah
Misbah
in Best Practices

There are four cornerstones in The Anaplan Way, with data being one of them. We all know that we should spend at least 1/3 of the project time on data alone because this is the cornerstone that we have no control over, and it needs to be thoroughly investigated and understood. One of the important things to consider about data is the format of the time in the source system. This article will explain the different formats Anaplan can accept in an import and the ones which it cannot and should be avoided.

Data in the source system can be at any granularity (i.e., daily, weekly, monthly, quarterly, semiannually, or annually). However, most of the planning happens either at the weekly level or at a monthly level. Therefore, this article will cover the import options for these two granularities only. 

Import mapping for months

If the intention is to get the data loaded into the system at month level granularity, here are the four options that you get within an import.

1. Dates. This format can be used when the source file contains dates. The question is, what is the format of the dates in the source system? Based on the format, there will be a further selection in the import mapping screen. 

i) Pattern with Separators: There are four choices given to us, but the number of formats that come within this bucket is vast.

 Y-M-D like 2020/06/01; 2020-06-01; 20/06/01; 20-06-01; 2020, Jun 1; 20, Jun 1; 2020/Jun 1; 20/Jun 01 etc.

 Y-D-M like 2020/01/06; 2020-01-06; 20/01/06; 20-01-06; 2020, 1 Jun; 20, 1 Jun; 2020/1 Jun; 20/01 Jun etc.

D-M-Y like 01/06/2020; 01-06-2020; 01/06/20; 01-06-20; 1 Jun, 2020, 1 Jun, 20; 1 Jun/2020; 1 Jun/20 etc.

  M-D-Y like 06/01/2020; 06-01-2020; 06/01/20; 06-01-20; Jun 01, 2020; Jun, 1, 20; Jun 1/2020; Jun 1/20 etc.

 Here, separators can be any non-alphanumeric character making the combinations go higher. Also, the 0’s in Month and Day needn’t have 0 prefixed with their numbers. In summary:

                 Y can represent 1, 2 or 4 digit year number

                 M can represent 1 or 2 digits or even Name

                 D represents 1 or 2 digits Day Number

ii) Fixed Position Patterns: There are eight choices that are available to us. Here we don’t have greater flexibility to play around with the formats. Here the month digits have to be exactly what is defined in the import

                YYYYMMDD like 20200601

                YYMMDD like 200601

                YYYYDDMM like 20200106

                YYDDMM like 20200106

                DDMMYYYY like 01062020

                DDMMYY like 010620

                MMDDYYYY like 06012020

                MMDDYY like 060120

iii) Custom Fixed-Position Pattern: You can have at least eight choices from Fixed Position Patterns here as well. If you wish to ignore any character within the format, “?” symbol is introduced on that location of the character.

         For example, 06/01/FY20 date format in the source file can be Imported as MM?DD???YY. For every character to be ignored you introduce the “?” Symbol

2. Periods. When the source file contains periods, based on the format of the periods in the source file, selections need to be made in the imports.

i) Pattern with Separators: Two different options under this pattern are given.

                  Y-M like 2020/06; 2020/Jun; 2020-06; 2020-Jun; 20/06; 20-06; 20/6; 20-6 etc.

                  M-Y like 06/2020; Jun/2020; 06-2020; Jun-2020; 06/20; 06-20; 6/20; 6-20 etc.

ii) Fixed Position Patterns: Four different options under this pattern are given.

                 YYYYMM like 202006

                 YYMM like 2006

                 MMYYYY like 062020

                 MMYY like 0620

iii) Custom Fixed-Position Pattern: You can have at least four choices from Fixed Position Patterns here as well. For example, 06FY20 can be imported as MM??YY.

3. Periods in specific year. This is the least used Import because the mapping needs to be changed every year, and this type of import can only load the data for one year. If you look at the example below, when you click on this option “Patterns with Separators” gets highlighted with one option in the box “M”. This M represents a one-or-two-digit month number or even month name. Once selected you will see “Year number represents”, and other fields under “Options” get activated.

Calendar year will respect the year that is selected in specific year and will Import the values into that particular year. For example, in this case, 2019.

Misbah_0-1593508262138.png

There can be scenarios when the fiscal year cuts across two calendar months. For example, Indian companies start their fiscal year in April and end in March the following Year. In this case “Start of the Fiscal Year” option and “End of the Fiscal Year” option comes in handy as the import will look at the main page of the model and will decide which year should be updated.

If the model calendar looks like April 19 - March 20, the start of the fiscal year will load the data into FY19 and end of the fiscal year will load it into FY20.

Misbah_1-1593508262154.png

Note: “Options” section also gets activated when selecting dates or periods option but practically, this becomes quite irrelevant there because we will always have years coming thru in the source file.

4. Match names. When the time format in the source system matches exactly with Anaplan timescale you choose this option. Anaplan’s default timescale is “MMM YY”. For Example, Jun 20.

Import mapping for weeks

1. Dates. There is no difference between dates for months and weeks. All the combinations remain the same.

2. Periods. When the source file contains periods: 

i) Pattern with separators: Two options are given.

                  Y-W like 2020/06; 2020-06; 20/06; 20-06; 2020/6; 2020-6; 20/6; 20-6 etc.

                  W-Y like 06/2020; 06-2020; 06/20; 06-20; 6/2020; 6-2020; 6/20; 6-20 etc.

                  Y represents 1, 2 or 4 digit Year number

                  W represents 1 or 2 digit Week number

ii) Fixed Position Patterns: Four options are given.

                  YYYYWW like 202006

                  YYWW like 2006

                  WWYYYY like 062020

                  WWYY like 0620

iii) Custom Fixed-Position Pattern:

                 FY20 06 can be imported as “??YY?WW” mapping.

3. Periods in specific year. Logic remains the same as it was for months; the only difference is instead of months you have weeks “W” in import mapping.

Misbah_2-1593508262172.png

4. Match names. When the time format in the source system matches exactly with Anaplan timescale you choose this option. Anaplan’s default timescale is WEEK<>Number<>FYYY like “Week 06 FY20”.

Now there can be a requirement where you have to change the week format from “Number” to “Week Commencing” or “Week Ending”. Does it mean that you need to change the source files too? The answer is no because Anaplan in the backend still remembers what is week 1 of the current year based on the selection that you make on the time page.

Misbah_3-1593508262179.png

Tagged:

Comments

  • ChadnrarACMA
    ChadnrarACMA

    Hi @Misbah 

    Very useful training ,material  on DATA( 1 of 4 Pillars) Import . Though you have written very well , I read couple of times to understand and clear my import obstacles in  on my way.

    I will read thoroughly after completion of my level 2 Conclusion

     

    Kind Regards 

     

    Chandrar 

  • Misbah
    Misbah

    @ChadnrarACMA 

     

    Thanks for the feedback. I have tried my best to explain it in such a way so that beginners too can understand. Probably need to work more to break it down further:)

  • neelam
    neelam

    How will I Map the below format while importing the data. kindly help as the format options does not give in MDYYYY format and custom format is not accepting for the same... what to do ?

     

    01-01-2019 02-01-2019 03-01-2019
  • Misbah
    Misbah

    If the format is MM-DD-YYYY then try Dates->Patterns with separators M-D-Y else D-M-Y and not Fixed Position patterns

Categories