Custom Time Mapping

sonpatel
sonpatel
in Integrations and extensibility

Hi Community, 

 

If the client has a custom time dimension file how would you go about mapping this file into Anaplan and then dynamically sum the sales by region by account by product by month? 

 

Find example data set below. I would normally do a custom time mapping to map from the clients time to Anaplan inherent time period but the fact that there are two years in the file is throwing me off. In my mapping I would only be able to choose Jan 19 OR Jan 20 to map to the custom JAN. How would I sum this without hardcoding anything? 

 

Thank you! 

 

 

Tagged:

Answers

  • rob_marshall
    rob_marshall

    @sonpatel 

     

    I would ask them if they would rearrange it to this:

     

    Column 1: Concatenate Region with Product with Account like Region_Product_Account

    Column 2: Date (Year and Month) - could be YYYY01 or YYYYJAN

    Column 3: Data

     

    This way, you will be able to create you List very easily, your transactional data module, and your SYS Properties module form one load (2 actions - 1 for the list, one for the transactional data, the properties can be figured out from the code).

     

    Rob

  • sonpatel
    sonpatel

    Hi Rob,

    Thank you for the suggestion! Unfortunately they are not able to rearrange the file. I've created a mapping module so far that maps the dummy time list to the Anaplan time list but I've having a difficult time summing. Any ideas? I've added screenshots for your reference 

     

  • ChrisAHeathcote
    ChrisAHeathcote

    @sonpatel 

    You could create a month list and include time(years) and this list in your import module. Set the list to include a property which corresponds to the month number and then in a staging module pull this month number into your data module, combine it with the time(year) and map it to monthly time period.

     

    Chris 

     

  • ChrisAHeathcote
    ChrisAHeathcote

    To map your year(time) and month list to a month time period

     

    1. Use Item(time) to pull the year into a line item

    2. Use RIGHT(Year line Item,2) to pull out the year number from the year time period. Ensure this line item is TEXT formatted

    3. Retrieve the month number from the properties of your month list. Use a number format.

    3. Format a line item as month time period and use the following to generate the output. PERIOD(DATE(VALUE(Year number),Month number,1))

    4. Use this line item to SUM into a module dimensioned my month time period

  • CommunityMember126793
    CommunityMember126793

    @sonpatel 

    Every incoming time data in the model should be **** as per Time Calender / Range, therefore in my view u may use the below method so that any change in the input file (month per say) will work:

    1. Create required lists like Product, Region, Account, YEAR, MONTH.... 
    2. Populate all those lists through Imports (only first occurrence in the list) (later can **** in process)
    3. A module with all those dimensions

    CommunityMember126793_0-1617107897248.png


    4. Import the csv file in this module

    CommunityMember126793_1-1617107938542.png   CommunityMember126793_2-1617107958466.png

    You will get all the data inside Anaplan by now

    5. Create a line item which will give Month and Year against the Volume like shown below

    CommunityMember126793_3-1617108002771.png

    Now, you can create a Time based new module and Lookup MonthYear in new module (you have to do some adjustments like to create few line items in Staging Module to finally reach to TimePeriod (e.g. Jan 19, Feb 19 etc.....)

     

    Hopefully this way your SUMMING UP problem against TIME also solved..

     

    Hope it will help

Categories