Implementing sumif in Anaplan using two different modules

I have one module containing of data from January 2010 to December 2020. In another module my dimension is not the Time period but a defined list of months. In these 12 columns of the second module, I want sum of January figures from data module to come in January column, sum of February figures from data module in February column and so on. Please help me with the same!

Best Answer

  • You need to map your 12 custom months to the standard periods by creating a "custom month" list formatted line item (list=your 12 months list) either in your data module directly or preferably in a "Time info" module. Enter the values manually or via a formula that reads the name of the time period, to put January (custom month) in each January cell (Jan 10, Jan 11,..Jan 20.)
    Your target module needs to be dimensionned by time (month) and by your 12 months list. The formula in your target module will look like: Data module.value[SUM: Time info.custom month]

Answers

  • Thank you for the reply. Looks like it will work fine for me.
  • Hi Jeremie,

    The solution you proposed works but creates a lot of sparsity due to having both the true timescale and fake timescale as dimensions when you only want to populate data in the fake timescale.

     

    In my case, my true timescale is in weeks and my fake timescale is in months. I want to sum the weeks into my months based on a mapping table. Do you have any suggestions for another way to do this without having weeks as a dimension in my target module?

  • Hello Jess,

    Another solution would be:

    1) Create a list with your 12 non-native months (Jan, Feb, Mar, etc.)

    2) Create a "mapping" module between your non-native months and the native weeks. For each week you assign a non-native month. You might have an issue with weeks that are across 2 months

    3) In the module where you have all your data, you create a line item called "Non-native month" and you pull the non-native month from your mapping module.

    4) You create your Summary module dimensionalized by your non-native months (but not by the native time). Then you pull the data from the main module by summing over the "non-native month" line item