How to find the average of the same month for different years?

tinathom
tinathom
in User Experience

I have 5 years of data. I need to find the average of the same month for the 5 years.
For example,

(Jan 19+jan 20+ jan 21+ jan 22 + jan 23) /5. Similarly i need to find for feb, march, April…

I used month and model calendar for the time dimension.
How do i develop this in anaplan?
thank you

Tagged:

Answers

  • TristanS
    TristanS

    @tinathom I would do the following:

    1 create a custom months list

    List name: Custom Month

    List items

    • Jan
    • Feb
    • Mar
    • …
    • Dec

    2 create a months time period system module that maps each month period to its corresponding custom month. Eg

    module name: SysMonths

    dimensions: time period months

    Line item: custom month mapping

    • Jan 2020 map to Jan list item in custom month list
    • Feb 2020 map to Feb list item in custom month list
    • Jan 2021map to Jan list item in custom month list
    • Feb 2021 map to Feb list item in custom month list
    • Jan 2022 map to Jan list item in custom month list
    • Etc

    3 create a module to calculate the ave. Eg

    module name: calc

    Dimensions: custom month, <other dimensions used by source module for average>

    line item: calc ave

    Formula: <source module>.<source line>[AVERAGE: SysMonths.custom month mapping]


  • ShubhamCh
    ShubhamCh

    Hi @tinathom

    Let's say, you want to calculate average of 'Your Line Item' for a month in different years.

    Jan 18 = Jan 19 + Jan 20 + ……. + Jan 23

    Simply use Lead function

    (LEAD(Your Line Item,12,0) +LEAD(Your Line Item,24,0)+ LEAD(Your Line Item,36,0) +LEAD(Your Line Item,48,0)+LEAD(Your Line Item,60,0))/5

    if you want to do

    Jan 18 = Jan 18 + Jan 19 + ……. + Jan 22

    (LEAD(Your Line Item,12,0) +LEAD(Your Line Item,24,0)+ LEAD(Your Line Item,36,0) +LEAD(Your Line Item,48,0)+ Your Line Item )/5

    This can be done using offset and lookup functions as well.

    Thanks!

Categories