Custom Time Mapping

sonpatel

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! 

 

 

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

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

@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

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

@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

4. Import the csv file in this module

   

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

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