How to create a loop formula

TahaU_*81964

Hi everyone,
I am stuck on a formula.

I have demands for 2021 and I need to calculate the demand forecast until 2030 by using the formula below

Demand 2022= Demand 2021*Growth rate 2022

Demand 2023= Demand 2022*Growth rate 2023

So how can I do that?

Thank you!

Accepted answers

ankit_cheeni

Hi @TahaU

You seem to be asking really good questions. I enjoyed the discussion on the last question as well.

While there is no default way to create a Loop function, the ask here seems to be simpler. I will not get into the details of dimensionality, I know you have a good grasp of that already. What you essentially need to do is bring previous year's value to current year (or year in consideration) and multiple it with the growth rate of current year (or year in consideration).

If there is a time dimension, a simple PREVIOUS should fetch previous year's demand to current
A bad way (just for discussion) is to create a system module that formulaically maps previous year to current, and then do a lookup basis this

What would be helpful is to look at functions that move values across time - LAG/OFFSET/LEAD/POST etc.

Let me know if this helps

TimothyThomas

Hi @TahaU ,

Ah, I see - sorry for oversimplifying it. In that case, I would echo @ankit_cheeni's suggestion of using a different function. I think LAG would do the trick here. In fact, it may even help simplify the formula. In this case, my example formula would instead read: LAG(Demand Calc,12,Demand) * Growth Rate

That should return the same month previous year's demand. In the case of your first year, it will instead just take that month's demand * that month's growth rate. Let me know if that solution works!

Thanks,

Timmy

All comments

TimothyThomas

Hi @TahaU ,

There are likely quite a few ways to accomplish this. Here's how I would do it:

If you don't already have a Time Settings module set up, create one (dimensionalized by time only). Create a simple formula to find your First Period which returns a boolean: CURRENTPERIODSTART() = START(). This will be set up as a Year time scale.
Create a new line item in your module called something like "Demand Calc." The formula would be: IF NOT Time Settings.First Period? THEN PREVIOUS(Demand Calc) * Growth Rate ELSE Demand

Hope that helps - let me know if additional help is needed!

Thanks,

Timmy

Grid_UX_-_Revenue_&_Margin_137567.jpg

ankit_cheeni

Hi @TahaU

You seem to be asking really good questions. I enjoyed the discussion on the last question as well.

If there is a time dimension, a simple PREVIOUS should fetch previous year's demand to current
A bad way (just for discussion) is to create a system module that formulaically maps previous year to current, and then do a lookup basis this

What would be helpful is to look at functions that move values across time - LAG/OFFSET/LEAD/POST etc.

Let me know if this helps

Grid_UX_-_Revenue_&_Margin_137567.jpg

AdwaitJha

Hi @TahaU ,

For, Creating a Loop Formula - an If/Else loop is a pretty straightforward approach provided your conditions are well defined and not too many as this may make the model slow.

Over here in this particular case, you can -

Create a System module to separate time between Actual/Current & Forecast using a formula, refer <
Time & Date Functions - it will give you better clarity> to use it as a condition for formula application.
Make a Calculation Module keeping Time (Model Calendar) in Column & 3 Line Items
Previous Period Growth – for the first period in the calendar (actual) – it’d be zero / for the second period onwards (Current & Forecast) grab it from “Forecast Growth” of the previous period. (IF-ELSE with OFFSET can be used)
Expected Growth Rate – for the first period in the calendar (actual) – it’d be zero / for the second period onwards (Current & Forecast) update through a UX /formula or Import, depending on your Use Case.
Forecast Growth – for the first period in the calendar (actual) – it’d be the actual value you received/ for the second period onwards (Current & Forecast) grab drive it through the formula “Previous Period Growth” * “Expected Growth Rate”.

Hope this helps,

Kind Regards,

Adwait

Grid_UX_-_Revenue_&_Margin_137567.jpg

TahaU_*81964

Hi @TimothyThomas,

Thank you for your reply. It is a good idea.

I have already tried something similar to your answer. My problem is that I need to calculate the monthly demand forecast and when I use the PREVIOUS function it refers previous month, not the same month last year.

I couldn't figure out how to deal with it.

Demand 2023 Jan = Demand 2022 Jan * Annual Growth Rate 2023

Taha

Grid_UX_-_Revenue_&_Margin_137567.jpg

TimothyThomas

Hi @TahaU ,

Timmy

Grid_UX_-_Revenue_&_Margin_137567.jpg

TahaU_*81964

Hi @ankit_cheeni,

Thank you for your great answers each time I ask a question!

I was able to do it with the OFFSET function.

IF NOT 'SYS00 Time Settings bu Month'.Current Year? THEN OFFSET('Demand Forecast', -12, 0) * (1 + Demand Growth Rate) ELSE Demand 2021

In this solution, I had to use 2 line items. Demand 2021 and Demand Forecast. Is there a better solution?

Grid_UX_-_Revenue_&_Margin_137567.jpg

ankit_cheeni

I think having two line items is fine. Honestly in my experience, breaking calculations in multiple line items is never a problem until it affects your space usage.

Grid_UX_-_Revenue_&_Margin_137567.jpg

Quick Links

Can you help?

Unanswered questions

Importing Ragged Hierarchies
Hello - I have a 2 level ragged hierarchy (yes, it's a chart of accounts), that for reporting purposes is used in 4 different models (yes, it's a P&L). What is the best way set this to allow one to be the "source of truth" and then import between the four models without creating actions that include ignores? It seems like…
Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…