Calendar Year To Date | Previous Calendar Year To Date

[Justin]

Hello,
Currently working on a widget comparing sales from Calendar Year To Date vs Calendar Year To Date (Last Year). I am working in Custom Mode and am trying to setup the periods so that when Current and Previous periods are comparing up to 09/24. What is the best way to setup my previous period so that it only includes up to 09/24 and not the entire month of September?

Here is how I currently have the periods setup, but this is only comparing CYTD through last completed month.

Current

Previous

The goal is to be able to compare CYTD (01/01/2019 to today’s date (09/24)) with last year CYTD (01/01/2018 to today’s date (09/24))

[Justin]

Here is something else I am trying:

Current (Used Days with Offset) - Going forward, I do not want to have to manually change dates

Previous (Used Days with Offset)

[StuartH]

Create a new custom period by clicking Add at the bottom of the period selection as follows:

All Period’s are built using the Special tab:

From - Select 1st Day of x months ago (0 for current month, -12 for last year)

To - Select Yesterday of x months ago (0 for current month, -12 for last year)

[Justin]

Hi @StuartH,

Thanks for the reply. Wouldn’t that be for Month To Date? I am looking for Calendar Year To Date, so Jan. 1 - Sep. 25. When I use my YTD (Period) in Variance (Mode), I use the Month To Date setting so that I am not comparing a completed September in previous calendar year. It is comparing the current date in the Current Period and Previous Period.

The problem I am running into is when using Custom Mode I cannot use the Month To Date setting. I am clicking Custom (Mode) | Custom and then selecting my measures. I then would like to have my period be Jan. 1 - Sep. 25 (today) and also have the same for previous year. This also needs to be dynamic, which is why I was using “Offset”.

I found something in the documentation, but not sure it will do what I need. I know it will give me the previous calendar year Jan. 1 - Sep. 2018, but will it give me Jan.1 - Sep. 25?

[StuartH]

Ah yes, my mistake.

In which case there are two ways depending on how you want to use it:

1. Set the Start to be the 1st of the year and the end to be yesterday. You’ll need to update the Start dates each year to the next year.

You also lose the ability to report monthly though if using them in a standard grid (with period across the top) - it doesn’t matter though if using it in Custom mode

2. Have two periods - one being Year to Date excluding Current Month and a second which is Current Month to Date. You can only use this in Custom as you’d have to add the columns together to get a total YTD to the day.

[Justin]

Thanks! I think that may do the trick. To make it dynamic, I was thinking I could use something like this for my Current:

Previous

My thinking is that using “Offset” would eliminate having to manually change the dates at the start of new year. Does that make sense or am I not fully understanding what “Offset” is?

[Justin]

Or using something like this for my Previous period.

[StuartH]

It does but each day will be a different number of days ago.

Hence today 266 days ago may be the 1st Jan, but tomorrow 266 days ago will be 2nd, then the day after the 3rd etc.

If you use the Current and Previous parts of the normal date range but fix the start date to the 1st Jan you only have to update it once a year.

[Justin]

Thank you for clerifying that. It is making sense to me now. I am not sure what I was thinking

[StuartH]

I’d like to see them do a First day of X month/year or something in the options under special.

[Justin]

That might be very handy to have as an option.

[ShaneAngel]

At Prochem, we actually operate our financial year on a calendar year, so our default YTD period is setup as follows;

This then does not need changing from year to year as it automatically picks up the current years January.

[StuartH]

Yes we do this too, but then you get all of this month last year not just the equivalent month to date.

There is the MTD option (click the *) in the standard grids but they don’t work in Custom

[KPaquette]

All,

Isn’t this what the fairly recent “Month To Date” setting is for? Whenever I compare YTD performance over prior year, I just use the YTD dynamic period (shown below).

After selecting a dimenson, then using Variance Mode, I can see that variance is; 12.19%, for example. However, using the “Month To Date” setting, my understanding is that the previous period scales according to the current period. So, in the case of today, my previous period will be Jan 1, 2018 - Sept. 27, 2018. So, when I do this, my variance % adjusts to 13.61%. This makes sense since my previous YTD period is now a smaller number (through the 27th, not the 30th).

[Justin]

Hi @KPaquette, when working in Custom Mode I do not have the option of selecting “Month To Date”. I do utilize the “Month To Date” setting when working in other modes though.

[KPaquette]

Understood,

I did not realize Custom Mode was necessary for other reasons. Now I’m on the same page.

[StuartH]

There are some odd choices in implementation in my view, not sure why the MTD option is part of the cog menu rather than the period selection.

If they’d done it as part of the period selection it would be usable in the various reports AND custom mode.

[Justin]

Have you run into any data issues when working in Custom Mode? I am running into issues with some of my columns returning “0” after entering my period for the measure.

In this example, the data is correct and all of my “Previous” columns are displaying valid data.

When I change the Period for Qty Per Default UOM to 01/01/2019 - 10/20/2019, all of my Previous columns stop working and only display a 0?

Any thoughts on why I can change the periods for my Sales, Qty fields without issue, but as soon as I change my Qty Per Default UOM period, the data is incorrect and is all 0s?
Here is what it looks like:

Thanks,
Justin

[StuartH]

If you’re using the Daily option in the Period selection you may run out of data and hence it’ll show 0.

What happens is that when the data is built it is pre-aggregated in periods to help speed it up.

Years & Months usually cover the entire data set but Weeks & Days only go back so far (as Phocas don’t expect you to want to analyse data by day that far back - takes a long time to build too).

They may be able to increase it depending on how complex your data is.

[Justin]

If I add another measure “Total Sales as of Now” using Invoice Sales for (Current Month), the other columns start displaying data again, instead of 0’s. It is almost like Phocas does not like every measure to use a manually defined period?