on 09-14-2018 2:43 AM
We're utilizing custom analytical queries for a lot of our core financial reports because it gives us more flexibility than financial statement versions for things like P&L categorization. Typically we've done them in excel, but I'm trying to steer our team out of that model so they can go spend their time around close in analysis not manual report building.
One of the reports that I'm struggling to replicate on the surface sounds pretty common and simple: P&L comparing the last 12 periods of actuals. While I can get the report to show the periods as the columns and the custom categories of our P&L, I'm having a difficult time figuring out how to get the report to automatically filter to show only the last full fiscal period and the 11 preceding periods. Having the user select the 12 periods they want every time is a pain and I'm imagining there has to be a way to do this. The thing that is tripping me up is that the fiscal year and period fields are not date centric within the custom analytical query and we have 13 periods (4 weeks a piece which do not align with calendar months).
Does anyone have any tips or experience creating a query/report with a rolling automated filter?
Thanks!
Ryan
I have forwarded your request to my contact in development. I will advise when he comes back with an answer.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Here is the guidance. If set up correctly, you can run it for any range of fiscal periods and years. In my test, I set it up to prompt for a beginning Period/Year and an ending one. https://blogs.sap.com/2018/07/26/year-to-date-rolling-forecast-how-to-create-a-year-to-date-rolling-...
Jeff
jeff.franz3 Thanks Jeff. How did you get it to automatically roll fiscal periods? I can get the report to prompt for Fiscal Period and Year to select manually, and can get it to display the fiscal periods as columns but don't know how to get it to do it automatically. For example, I'm currently a week into P10, when the report runs I want it to show P9 2017 to P9 2018, not posting date records from today thru 365 days ago as shown in the blog. I hope I'm missing something, but I don't get the option to use date prompts measures or expressions with our fiscal year period field and don't know of an expression with posting date which will tie it to fiscal period that is non-month based.
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.