Parent & Child Period Ranges - Current Month Issue

One issue we always face is that if I have a custom report where i’m trying to show both Yesterday and Month to date, I can set both ranges to work independently as “Yesterday” and “Current Month”.

However if I run the report on the 1st August I would get 31st July in the Yesterday part and 1st August as the Current Month.

If I build the report today as Previous Month to make it work, tomorrow I’ll get 1st August in the Yesterday section but July in the Month section.

Systems I’ve used in the past allow you to pick Yesterday as the base, but then use Parent as the basis of the filter. Hence I could say use Yesterday but go up a level to Month and use that month, rather than forcing to a fixed Current or Previous.

4 Likes

@StuartH why is showing yesterday 31st July and MTD 1st August a problem? So you would like a “yesterday in current month” option where it returns today if today is the first of the month?

Actually the opposite, I always want yesterday, but I want month to date to be the current month when yesterday and current month are the same month. If yesterday was the last day of last month, I want the whole of last month as the month to date

Hi @StuartH, I think I understand. It’s not something I’ve come across before. I’ll log a suggestion and gather interest however I think it’s unlikely that this will be developed.

That’s a shame, I’m sure others have a a similar requirement - maybe there is a work around I’m missing.

@will.marriott I run into something similar when I’m dealing with budgeted data. At the beginning of a new quarter, I want the new quarter’s information to display, but it won’t display currently on the first day of the quarter for a quarter-to-date measure, because I have that set up as “1st day of the quarter to yesterday” for both budgeted and actual. So, to display the budgeted goals correctly, I have to go into seven separate dashboards and change settings to “today” just for one day to display budgeted data correctly, then go back in the next day to change all seven dashboards back to “1st of the quarter to yesterday”. There are definitely higher priority things to be developed, but it is a bit of an annoyance to do that double work.

1 Like

Totally agree on this. It is a major problem at month end / cross over. I basically never get an end of month report.

I have a number of favorites that run daily and have a period of Current Month. Works great except for the start of a new month. Returns zeros and does not give a final report for what is now the previous month. Hopefully this makes sense. I am thinking there must be a way to still use current month as the period but also have it return current day minus 1.

1 Like

Hi @will.marriott, You may recall we discussed a similar premise after we had a call, where we would need variables to control the custom modes date ranges. I proposed that variables to be used as offsets (in this case the parent date as @StuartH discusses ) would be the driver to show the previous month/day. I may be barking up the wrong tree here but sounds very very similar to the requirement we discussed. So sorry in advanced if I am confused.

Example
Day 1 of October, we have 100 people looking at month end close (30 Sept), I have had to double up on the number of custom widgets, so we have a widget showing prior month versus another showing current month. This could be handled in one custom mode widget if a variable was introduced as a “Parent” which governs the widgets custom date ranges.

Custom Mode: Rather than using a set date range, we could have the option of a variable which would be the point in time we wish to view. In @StuartH 's example the parent could be 1st October, but the custom column would be offset -1 to show yesterday. Implementing this in a user friendly fashion will be problematic, but relative referencing is what B.I. is all about. Examples in other systems are:

  • Peek (use offset)
  • Lag (use offset)
  • Lead (use Offset)
  • Or more exotic is MDX:
    [Date].[Calendar].CURRENTMEMBER.PREVMEMBER.NAME

It would be great if the custom mode period options had something like this:
Date Variables created based on the databases defined Period Types:


“MyVariable” is used (discussed shortly) and bound to the “Month” Period Type, it will only allow the choices of month to be entered.

“MyOtherVariable” is used (discussed shortly) and bound to the “Day” Period Type, it will only allow the choices of Day to be entered.

So how do we create the variable?
I would suggest that this could be done one level up as part of the calculations definition:


This then adds an extra line, but instead of defining the measure, you define the date:

Note: You should be able to either type in the date (fixed) or potentially have a volatile expression such as GetDate() with offset up there as well!

By having this at the top of the Custom Mode, it allows flexibility with having a “Parent” date (based on the DB’s Underlying period types see below).

My biggest issue with custom mode is at year end/month end numerous widgets need to be updated. Think of what “Current Quarter” means, its relative to the month you’re in, using January as the starting point, Current quarter for October would be Q4, but looking at month end (Sept) it would be Q3, since custom modes offsets are ALWAYS relative to the current date, we are constantly adjusting widgets or adding additional period types and slowing down the database build time. Having a master value to base the dates off like this should help.

And as for my requirement it is one step further (but happy to wait!) which is allowing the user to enter the variable in the dashboard, this would revolutionise the dashboards into being truly dynamic with what if scenarios etc as one variable can alter the entire result set.

I appreciate there is quite a lot of things to iron out, but thought I should put my thoughts down on this post as I think this will reduce the time to alter month/year end dashboards and widgets considerably.

Here is what I meant by Period Types, in the designer of the DB, these are what the variables would be allowed to attach to:

Happy to discuss further if you believe this is a potential option.

2 Likes

Wow, that’s a highly technical work through.

My old reporting system did parent/child/grandparent/grandchild levels.

E.g: (technically the levels are relative to the base selected)

If selecting a Month…

Grandparent = Year
Parent = Quarter
Child = Day

If selecting a Day (there is no child - unless you start to include time)

Great-Grandparent = Year
Grandparent = Quarter
Parent = Month

1 Like

Blue sky thinking here, but if we could adopt variables in this fashion I think it would suffice all bespoke requirements of Grandparent hierarchies as I mentioned on the note, you could hard fix the date or use offsets or getdate. I am sure Phocas will eventually catch up, but my only other workarounds are putting a shed load of additional period types in (such as working out what the Qtr was of last month).

Regards,

Jon

1 Like

In my example you could essentially select a range by DAY but show it in the grid by MONTH or YEAR.

Seem to be getting some traction on this now as expected.

Really think this is a major win for Phocas if you move forward with it.

@StuartH @JonKemp there is definitely some interest. Thank you for your contributions to this subject.

There seems to be numerous requirements being listed in this thread. I’ll try to simplify them and list them here.

  1. Users want to see the whole of last months values on the first day of the current month. Simply put, user wants to choose the offset to be from yesterday rather than today. This would be available for all period types e.g. weeks, quarters etc.
  2. Users want to see values offset from a variable date. Choose a date and the period is worked out by offsets from that date. This should be available in defined periods and custom mode and in dashboards. (JonKemp leaving aside variables in custom mode as that’s covered elsewhere)
  3. Users want to see a range based on day but summarised by a different period type. For example, I want to see data from 15th January to 15th September but summarised by month. January and September would only have ~15 days of data in them.

Does that sum it up?

2 Likes

I’d say that covers it yes, thanks for picking it up again.

1 Like