formula revision for QTR last year

Hi Community,

I would like to ask advice on formula revision of my clients report. Below is the target module. The column of last year is where we are encountering a problem.

Requirement: it is supposed to pick up the value from the source module but the total QTR of last year.

Target module: OUT007

In the target module if I select Q2 FY22 it should capture the value from the source module Q2 FY21. As seen below. It should be 26, 759 instead of (53,517).

Source module: Cal001

The current formula on the target module seems to be referenced to the wrong target module.

Current Formula of Last Year column: MOVINGSUM('OUT006 Corporate Expense - Net MTD'.Actual CY, -11 - MonthNum, -12, SUM)

Do I just reference it to the correct source module and retain the formula? Or revised the entire formula to capture the total per QTR.??

Hope you can provide some insights on the matter. Thank you in advance for any comments regarding the matter.

Regards,

Accepted answers

BrunoRodriguez

Hello again,

I would base my decision in the following points:

Do you need the data to go into a single cell? (i.e., if the data does not need to go into a module with time)
Will this need to be recalculated for every quarter?

If you are taking that data and placing it somewhere else in such a way that you will only need the numbers for that specific quarter, you could go with MOVINGSUM as you could make a SUM of certain months for every cell.

On the other hand, if you will need this to be calculated in a recurrent basis, it may be wiser to use LAG or OFFSET.

Hope that made it a bit clearer. Just play with the formulas based on your requirements.

All comments

andrewtye

Have you tried using lag?

Andrew

Jsdeloria21

hi @andrewtye,

no not yet. would you recommend using that? Also can i capture the QTR total using lag?

Appreciate your time to provide some insights.

Regards,

BrunoRodriguez

Hello Jsdeloria21,

As you have been suggested below, have you tried the following?

Lag(Line item, 12,0)
Offset(Line item, -12,0)

As for your formula, I would check the time period that you are using for the movingsum as it may not be the correct one.

Hope that helps!

Jsdeloria21

hi @BrunoRodriguez ,

No not yet, I have not tried Lag formula. shall I keep the movingsum formula to capture the total per QTR?

Appreciate your feedback.

Regards,

BrunoRodriguez

Hello again,

I would base my decision in the following points:

Do you need the data to go into a single cell? (i.e., if the data does not need to go into a module with time)
Will this need to be recalculated for every quarter?

On the other hand, if you will need this to be calculated in a recurrent basis, it may be wiser to use LAG or OFFSET.

Hope that made it a bit clearer. Just play with the formulas based on your requirements.

Jsdeloria21

hi @BrunoRodriguez ,

Yes in a single cell labelled Last Year. I was able to direct it to last year using Lag formula see below. i use the suggestion given LAG(line item. 12.0). below my selection is Q2 FY22. so my result should be Q2 FY21 actual.

its now going to Jun 21. But I need to capture only the Actual version and total for Q2 QTR.

I think i need to refined my formula to get the Actual Q2 total.

Regards,

Quick Links

Can you help?

Unanswered questions

Set text encoding format for anaplan connect script upload
Hi, I would like to specify in an Anaplan Connect script that UTF-8 text encoding should be used for the file I am uploading. Does anyone know the command for this, or another way to make this happen? Thanks!
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…