formula revision for QTR last year

Jsdeloria21
Jsdeloria21
in Modeling

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,

Tagged:

Best Answer

  • BrunoRodriguez
    BrunoRodriguez
    Answer ✓

    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.

     

     

    Kind regards,

    Bruno Rodriguez

Answers

  • andrewtye
    andrewtye

    Have you tried using lag?

    Andrew 

     

     

  • Jsdeloria21
    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
    BrunoRodriguez

    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!

     

     

    Kind regards,

    Bruno Rodriguez

  • Jsdeloria21
    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,

  • Jsdeloria21
    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.

     

    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,

Categories