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

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…