Custom Period - Anaplan time mapping

Kanishq17
Kanishq17
in Modeling

Hi Community,

 

There is a use case we were provided, 

so data was loaded against 2 dimensions with a custom period list 

Kanishq17_0-1591900087455.png

also in a separate module the start and end date of each period is provided

Kanishq17_1-1591900195364.png

the start date of period 1 and end date of period 13 is aligned with the time calendar FY20(1-Jan to 31 Dec)

 

the task is to get the values for the 2 dimensions against standard timescale (months) based on the periods to date mapping, 

Kanishq17_2-1591900423973.png

also the values in each period are assumed to be distributed equally for duration of the period, eg for f and India value for Jan 20 will be 1000(entire P1 is in Jan)+(2000/duration of P2)*no.of days P2  in Jan

 

 

 

Tagged:

Best Answer

  • anikdas
    anikdas
    Answer ✓

    Hi @Kanishq17 

     

    There are two ways to solve this. - none of them are pretty.

    • First approach is to convert Periods into Days in Native Timescale. Owing to the implication on size, I would not want to prescribe this approach.
    • Second approach is to use the mapping to derive the # of days in a period against the months of Native Timescale. This involves a 4 layer if formula (which can be optimised further). Below is the approach with screenshots (blueprints attached).

    First create a module to do the Period to Native Month Mapping to identify the # of days falling in each month

    Screenshot 2020-06-12 at 10.52.41 AM.png

    Second, create an intermediate module to transform data from Periods to Native Months. You need to use both dimensions together here.

    Screenshot 2020-06-12 at 10.53.49 AM.png

    Lastly, Create the output module to calculate the results

    Screenshot 2020-06-12 at 10.53.56 AM.png

     

    P.S. In the period mapping that you posted, P12 and P13 has overlapping days. Although the formula caters to such occurrences, you may want to check that. 

     

    @Kanishq17  Let me know if this works for the problem statement. If it does please mark this as a solution so that others can refer to this.

Answers

  • Kanishq17
    Kanishq17

    Hi @anikdas ,

     

    Oh, I didnt check that start date and end date of periods were overlapping, anyway thanks a lot for the solution, just a few doubts.

    - Why have you used MAX function here ? 

     

    Anyways I tried and it is working good. Thanks a lot

     

  • anikdas
    anikdas

    @Kanishq17  There was one part of the if statement, which will calculate negative days if there is no overlap between period dates and month dates. If memory serves correct, it is the last part of the if statement. I thought Max is probably the easiest solution to that.

Categories