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?
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).
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
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.
Every incoming time data in the model should be tied up 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 tied up 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..