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.

Accepted answers

rob_marshall

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

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

Created my input module:

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

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

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]

Hope this helps,

Rob

All comments

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

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

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

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

rob_marshall

@rhightower

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

Created my input module:

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

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

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]

Hope this helps,

Rob

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.

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

Caleb

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…