In relation to a post in the Tips & Tricks I thought I’d raise the Product Suggestion here.
To avoid the need to update each stream in Custom Mode when needing to roll back a report to a previous period - could be 10+ streams to set to fixed dates.
In the Custom mode, when you click + to add Calculation, Measure or Variance.
Why not have a 4th option for “Base_Period”?
This could be used to set a date that is different to today for any Offsets in the report to use.
By default it would be Today / This Month / This Year.
However you could then set this to a specific Day / Month / Year so that the offsets use that as the base date and then -x days / months / years in their calculations.
2 Likes
Hi @StuartH, thanks for your suggestion.
To confirm I understand your suggestion, you would like custom mode column measures to use offsets from a configurable date, rather than just today?
This is because you have a custom mode favorite you’d like to see from a previous period and there are a lot of columns to change manually?
Exactly, yes.
The suggestion comes from a thread in the How To section where another user has this issue. This was my suggestion of how it would be fixed.
Hi @will.marriott ,
We also desperately need this. The static date ranges on a custom are very restrictive, having them dynamic would remove a large amount of headaches for us. I would say the dashboard reporting the business use is around 70% custom reports where they want to change the date, so having this associated to the dashboard that it is a relative reference to current date range would be a dream come true.
Examples of custom reports we have:
As you can see the above custom report is using the financial statements database to derive the layout the management require. This uses a custom period type of “Fiscal year to date (exclude current month)”. This works well for the current month, if however the user wants to look at last month, changing the date range on the dashboard does not impact the custom widget. Also, the solution of using custom period types has its own headaches…
Fiscal year to date (exclude current month) / Calendar year to date (exclude current month) defined periods
I have this set up as a defined period, only problem is if you look at the Fiscal YTD: This uses offsets, the date range logic is Sept (Most Recent) to Offset -1, this works for Feb to Dec, but in January no reports work as the logic would become Sept (Most Recent) to Aug prior year = 0 rows.
Having the date ranges compatible with the dashboard date range would remove a lot of custom period types, provide users the ability to use the one dashboard to traverse different time periods and crucially speed up build time of our databases as we could remove a lot of workaround “Period Types” we have to aggregate against on the DB (estimate a 40 minute reduction in build time).
Hope this makes sense and helps define the requirement at a nuts and bolts level.
Kind Regards,
Jon
Thanks for your additional input @JonKemp.
I’ve logged a suggestion that custom mode measure periods can be offset from a configurable date.
There is an existing suggestion to cater for “MTD or YTD completed months”. Would would you expect to see when viewing that in January? The completed last year or the current month?
HI @will.marriott,
Thanks for digging into this, talking about dates can make you cross eyed.
So we have two to contend with, Fiscal year & Calendar year. In both cases the first period should appear as follows:
Fiscal Period (Sept start of new fiscal year)
Lets say today’s date is Sept 12th 2018, the custom report using “FYTD Exclude Current Month” would need to be Sept 17 - August 18. If today’s date was Oct 15th 2018 the date range would be Sept 18 - Sept 18.
Calendar year
Pretty much same logic. So if its January 12th 2019, it would need to report January 2018 - December 2018. If its February 15th 2019 the range would be Jan 19 - Jan 19.
Both of these would want to be controlled on the dashboard to support relative references. So if the date was changed (in first example) from Sept 12th 2018 to Aug 14th 2018 the “FYTD Exclude Current Month” range would become Sept 17 - July 18. I have created an example of how teh date range should work dependant on how the user selects.
@JonKemp that makes the most sense to me. A completed months period should just show completed months. I’ve added your comment to our logged suggestion. We are discussing working on query improvements and this may be included. No timeframe as yet though.
