formula revision for QTR last year

Jsdeloria21
Regular Contributor

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

Jsdeloria21_0-1657519157045.png

 

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

Jsdeloria21_1-1657519157058.png

 

 

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,

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoRodriguez
Regular Contributor

Hello again,

 

I would base my decision in the following points:

 

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

 

 

Best,
Bruno Rodriguez

View solution in original post

6 REPLIES 6
andrewtye
Master Anaplanner/Community Boss

Have you tried using lag?

Andrew 

 

 

Jsdeloria21
Regular Contributor

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
Regular Contributor

Hello Jsdeloria21,

 

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

 

  1. Lag(Line item, 12,0)
  2. 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!

 

 

Best,
Bruno Rodriguez
Jsdeloria21
Regular Contributor

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
Regular Contributor

Hello again,

 

I would base my decision in the following points:

 

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

 

 

Best,
Bruno Rodriguez
Jsdeloria21
Regular Contributor

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.

 

Jsdeloria21_0-1657525538843.png

 

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,