Input Module as Quarters to apply to Months in Calculation Modules

rhightower
rhightower
in Modeling

Looking for a way to have a data input module of 1Q, 2Q, 3Q, 4Q as one axis and years as the second axis (2020-2025). This grid would be the input module for end-users within Anaplan.

The calculation module uses a monthly time scale. I want the formula to retrieve the value from the input module for the quarter/year which corresponds to the relative month. The equivalent in Excel would be a INDEX(MATCH). I've removed the timescale from the Input file which causes failure in my attempts with LOOKUP/SUM in Anaplan. Community thoughts appreciated.

Example:

Jan-2020, Feb-2020 and Mar-2020 pulls data from 1Q2020

Dec 2021 pulls data from 4Q2021; etc.

Tagged:

Best Answer

  • rob_marshall
    rob_marshall
    Answer ✓

    @rhightower 

     

    Ok, there are two ways of doing this, the easy way and the hard way or I should say more complex.  Correct me if I am wrong, but I am thinking you are thinking of this input from an Essbase perspective where your years and quarters are different dimensions.  If so, the "easy" way is to just input the data at the quarter level and be done with it, no fake quarters.

     

    2020-07-01_10-00-39.png

     

    2020-07-01_10-01-07.png

     

    Now, they way you have described it, you will have to use "Fake Quarters".  So I created a Fake Quarters list:

    2020-07-01_10-02-39.png

     Created my input module:

    2020-07-01_10-03-44.png

     

    2020-07-01_10-03-58.png

     

    Now, how do I get this to months?  Create a SYS Time Month module where you have the following defined:

    Quarter: item(time) with the format Time Period Quarter

    Quarter Text: left(name(Quarter),2)

    Fake Quarter: finditem(Fake Quarters, Quarter Text)

    FY Year: item(time) with the format Time Period Year

     

    2020-07-01_10-05-31.png

     

    2020-07-01_10-06-51.png

     

     

    If you go the easy way, you will still need the above, but only the Quarter line item.

     

    For the reporting, create a module using Month.

    If done the easy way, you just have to do a lookup for the Quarter in the SYS Time Filter module

     

    2020-07-01_10-13-11.png

     

    The harder way:

    the formula for the line item will be: 'rhightower input - harder'.Input[LOOKUP: SYS Time Filter.Fake Quarter, LOOKUP: SYS Time Filter.FY Year]

     

    2020-07-01_10-09-37.png2020-07-01_10-09-49.png 

     

    Hope this helps,

     

    Rob

Answers

  • nathan_rudman
    nathan_rudman

    You can easily do this by creating a List of quarters and crossing that list with a yearly timescale in a module.

     

    Then you have to map your custom Qs to the real ones. The easiest is to rebuild a date like this:

    date LI = DATE(YEAR(ITEM(TIME)),Q Mapping,1)

     

    Then you can SUM of that to bring it to a monthly timescale

  • rhightower
    rhightower

    Are you suggesting a list of the discrete quarters (1Q20, 2Q20, 3Q20, so on...)  or only 1Q-4Q paired with the yearly timescale? Would this be a Boolean to identify where each quarter corresponds to which year? 

     

     

  • nathan_rudman
    nathan_rudman

    Only 4 Qs, which year they are depends on the yearly dimension they are crossed with in the module

  • rhightower
    rhightower

    I'm lost when it comes to when/how the Date LI formula comes into play - specifically the "Q Mapping" part of the date formula. Is this formula within the Module with the 4Q's crossed with the Yearly Time Scale - if so, what is intended by Q Mapping?

     

    Updating to add details to aid in my understanding of this:

     

    I have Module A:

    Timescale: Yearly 2020-2025

    List Dimension: 1Q, 2Q, 3Q, 4Q

    Line item: Is this where the Date LI belongs? If so, how does the Date formula Derive the month without a circular reference within the module?

     

    Or Is there a Module B which derives the Month:Quarter relationship to be used in the Date LI Formula? If so, how do I create a 12 month timescale to map the quarters (instead of 72 for 6 years of time scale) .

     

  • CalebW_77
    CalebW_77

    @rob_marshall this is super helpful and works really well. However, to add to this - I used this in a model where the time settings are set to month without quarters, so I took the 'Harder' approach but I had to first create a time range with quarters in it (See image below). This then allowed me to then create the 'SYS Time Filter' staging module, and use quarters as a format, which I then linked back to months.

     

    CalebW_77_0-1602709019060.png

    Hope this helps any body else who is hoping to do something similar.

     

    Caleb

Categories