Summation using Months and Year

subodhp
subodhp
in Modeling

Hi All

 

I have a piece of data which is based on unique keys. Each key has data for multiple months. Also there is another column which specifies the financial year. I have created an intermediate module where each transaction for a key is flattened out by month. So i have line items which derive the month value in text  ('01',02',12' etc in 'mm')and year value ('15','16' etc in 'yy')in text format for each transaction.

I want to be able to create another line item from the month and year which combines them together so that it creates a period in mmyy format which i can then use sum the line items together by the financial period. For example all 0116 should sum under Jan 2016 time dimension 0218 should sum under Feb 2018 time dimension. Will i need to create any list for month year? how do i go about this?

 

Thanks

 

 

Tagged:

Best Answer

  • PaulRitner
    PaulRitner
    Answer ✓

    Hi,

     

    I use one of the following two options, neither involve a list:

    1. Create a line item formatted as a date and use the Date function to convert your months and years into a date.  Dates can be referenced by SUM statements to align data with Anaplan Time Periods
    2. Another option is to create a line item formatted as Month (...Anaplan Time Period) and use a fomula to calculate the Time period... then you can reference that time period in a SUM statement.

    If you are starting with data that looks like this:  ('01',02',12' etc in 'mm')and year value ('15','16' etc in 'yy')

     

    Then some of the statements might look like:

    1. Date_Line_Item Formula:  DATE(VALUE("20"& Year_Value_Line_Item), Value(Month_Line_Item), 1)
    2. Period_Line_Item Formula:  PERIOD(Date_Line_Item)

    Paul

Categories