How do you set up a trailing twelve period report when it crosses fiscal years?

In the SEI Excel reporting, is there a way to have a trailing twelve month report that crosses fiscal years without having to have 12 hidden columns that you sum to populate your TTM column?

As an example, if you were reporting for June 2021, you would want to include July 2020 through June 2021 in your TTM column.

In the Formula Wizard you specify where to get the fiscal period and fiscal year, but can you pull both July-December 2020 AND January-June 2021 into one column?

Thank you.

Cindy

Parents
  • You could also create two formulas, the first one will pull from FY 1, periods 6 to 12 and the second formula for FY 2, periods 1 to 5. 

    Create the two formulas in separate cells using the formula wizard then combine them into one cell like this:

    =(necaccess(formula for FY 1, FP6:12)) + (necaccess(formula for FY 2, FP 1:5)) 

  • in reply to Chris Grigg

    Thank you for your reply.  I was wondering if I could use two different formulas but didn't try because I am looking at a lot of variables (reporting code, period, year, company).  I guess I could set it up for one and then copy it in after a + sign.  I also would have to figure out how to make it dynamic based on the period and year my user entered.  I might try this in a future report.

     I ended up going with 13 hidden columns to the right of my report.  That way I could use IF formulas to bring in the correct year and period based on the one my user put in the parameter field.  It's not as slick but it works.

Reply
  • in reply to Chris Grigg

    Thank you for your reply.  I was wondering if I could use two different formulas but didn't try because I am looking at a lot of variables (reporting code, period, year, company).  I guess I could set it up for one and then copy it in after a + sign.  I also would have to figure out how to make it dynamic based on the period and year my user entered.  I might try this in a future report.

     I ended up going with 13 hidden columns to the right of my report.  That way I could use IF formulas to bring in the correct year and period based on the one my user put in the parameter field.  It's not as slick but it works.

Children
No Data