Custom YTD through MTD

howto

#1

Using the YTD variance early in a month provides inaccurate results since the prior year runs to the end of the month. I need to create a custom period that includes month and day so that I can add prior YTD to the current MTD for this year and the prior year.
Has anyone been able to combine the two types of periods?


#2

Ed, I was not able to use a custom period for this but if you use year to date in variance mode and make sure you check Month to Date, under the cog wheel up top, then it will correctly compare the two periods. If you search the doc for month to date you will find information on this feature.
This is fine but I would like to change the column labels instead of current and previous, which I can do in custom but then the month to date feature is not available.
Please correct me if this is not correct.


#3

Bill,
That’s what I have found too. The MTD feature is nice when you’re in Period view, but there’s no way to use this feature in Custom that I can find. There’s several instances where I need to use the custom view to display what I need, but I can’t use the MTD option to correctly compare against last year.


#4

@aaron.roma and @billw
I think I just answered the same question about YTD/MTD in Custom Mode, but I’ll throw this here, too:

I believe in custom mode if you use the YTD option, it automatically makes it month to date. That’s what it does for us, at least:


#5

@tim.janes Are you sure it’s making the Previous Period YTD? That’s where the problem comes in. Typically, the “Year to Date” period is setup as a monthly type period. Without the “Month To Date” option selected under the “Gear” icon, then the Previous Period is showing the full month. If you turn on the “Month To Date” option, it works fine and shows the Previous Period as a true YTD figure. The issue is though, as soon as you change to Custom mode, you loose the “Month To Date” option, so it reverts to a full month for the previous period. If yours truly is showing YTD for the previous period in custom mode, I’d like to see how your “Year to Date” period is defined. Can you verify that you’re seeing a true YTD value for the previous period?


#6

@aaron.roma
You’re right, that year-to-date in customer mode only works for this year, since it does run through the end of this month. So there is no data for the rest of April this year, but there is a full month of data for last April. My original misunderstanding was that I read your original post on this thread that your information for year-to-date only went through the end of LAST month, which would be a different issue. However, I did not realize that year-to-date previous in Customer Mode runs through the end of April last year, too. So, thanks for that!

I tried building a custom defined period in the database editor using the “Special” tab when defining periods to create this:

You would think that it would work… but it’s still not pulling last year-to-date correctly, which it curious, because when I click into it, it’s showing all the correct dates to pull information for! I realize that this would still have to be updated every month for how many months to go back, though.


#7

There is a reason why you’re trying doesn’t work. (I’ve already been there…) By default, the daily data is only summarized back 30 days. So when you’re creating a “Day” based period, you will only see data for the past 31 days. You can actually increase this number in the database design, but from what I can tell, it has a significant impact on performance. I believe the MTD option was to overcome this limitation, and it works great in certain modes. The issue is this MTD option really needs to be extended to the Custom mode.


#8

We requested the day summation go back 397 days so we can view YoY results for the current month.


#9

@EdKondrat Did you notice any particular performance hit after this change?


#10

No I did not

<img usemap="#map_da67ff23-ac24-4bef-bec7-8d9bb2e5f11a" src="/uploads/phocassoftware/original/1X/89407715ec262b14d956774fae4246c71ea10dd8.png" width=“543” height=“170” border=“0” alt="Canyon Pipe & Supply, Inc
Ed Kondrat
Controller
P: (623) 544-5224 Ext: 6203 | M: (602) 561-0850
E: edk@canyonpipe.com | W: www.canyonpipe.com


#11

The performance hit would be on the database rebuild side - not on the user interface. Your database rebuild time could increase significantly when doing this based on factors such as the speed of your server and number of transaction rows and dimensions in your databases. While one customer may not experience adverse build times in doing this, other customers may.

(sorry - just wanted to throw this disclaimer out there :slight_smile:)