Highlighted
New Contributor

Input Module as Quarters to apply to Months in Calculation Modules

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.

5 REPLIES 5
Highlighted
Community Boss

Re: Input Module as Quarters to apply to Months in Calculation Modules

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


Nathan Rudman, Anaplan Model Builder
Highlighted
New Contributor

Re: Input Module as Quarters to apply to Months in Calculation Modules

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? 

 

 

Highlighted
Community Boss

Re: Input Module as Quarters to apply to Months in Calculation Modules

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


Nathan Rudman, Anaplan Model Builder
Highlighted
New Contributor

Re: Input Module as Quarters to apply to Months in Calculation Modules

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) .

 

Highlighted
Master Anaplanner/Community Boss

Re: Input Module as Quarters to apply to Months in Calculation Modules

@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