x Want to participate? Join the group to interact
  • Replies 4 replies
  • Subscribers 42 subscribers
  • Views 1932 views
  • Users 0 members are here
Browse Forums
Announcements
237
topics
29
replies
General Discussion
5k
topics
13k
replies
Sage Data & Analytics
56
topics
95
replies
Sage Enterprise Intelligence
210
topics
416
replies
Sage Intelligence Reporting
121
topics
322
replies
Cindy Ventura

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

Posted By

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

  • Don't know what your fields are called, but you could try using dateadd>

    [TABLE.DATEFIELD] BETWEEN DATEADD(day, -365, @@TODAY) and @@TODAY will give you a rolling 12 month window, ending with today's date. Leap years will catch it out.

Reply

  • Don't know what your fields are called, but you could try using dateadd>

    [TABLE.DATEFIELD] BETWEEN DATEADD(day, -365, @@TODAY) and @@TODAY will give you a rolling 12 month window, ending with today's date. Leap years will catch it out.

Children
  • in reply to Jules@Delta

    Thank you for your reply.  Our fiscal year is 13 four week periods so a lot of the date formulas don't work for us.  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.

*Community Hub is the new name for Sage City