Year on year calculation on a monthly indicator

[Gareth Davies1]

Not entirely a question, something that isn't very well documented and while YoY was supported in legacy dashboards that functionality hasn't made it over to platform analytics year.

A popular question is "How are we performing compared to this point last year?" on things like incident count, this is my current best answer for a monthly indicator.

There are two parts to the trick.

1. Calculating when the data collection was done, for us it's on the first of the month so day one of the month after the month being collected.
2. Formula indicators don't appear to be interval sensitive so they behave as daily even if the underlying indicator is monthly. This causes issues as only one day of the month will actually have a value. To handle this, regardless of the current day of the month we force the date to be the day of the month scores are collected on. I don't entirely know why this is necessary, but if I don't do it it doesn't work. I expected it would just shrug and move on if there wasn't a score on a specific day but no, it reports no data at all.

So breaking that down using last month as an example...

October 2024 is the month's data we're looking for and it's currently 26-11-2024. That data was collected on 01-11-2024.

Last year

score_start date is xx-10-2024

Subtract a year to get xx-10-2023 

Add a month to get xx-11-2023

Force the day of the month to 1 as that's the day we collect to get 01-11-2023. 

This year

score_start date is xx-10-2024

Add a month to get xx-11-2024

Force the day of the month to 1 as that's the day we collect to get 01-11-2024

var lastYearDate = new GlideDateTime(score_start);
lastYearDate.addYears(-1); //Subract a year from the score date
lastYearDate.addMonths(1); //Add a month because the score is captured in the following month
lastYearDate.setDayOfMonth(1); //Score is captured on the first of the month
var thisYearDate = new GlideDateTime(score_start);
thisYearDate.setDayOfMonth(1); //Score is captured on the first of the month
thisYearDate.addMonths(1); //Add a month because the score is captured in the following month
pa.getChange($[[Moderate Incident: Number of resolved incidents]], lastYearDate, thisYearDate); //Get the difference between this year's score and last year

Using that formula indicator with a YTD SUM aggregation gives me an accurate "What's the difference in incident volumes between this year and last year?"

It seems to work, the numbers add up to the same as I get in Excel, so I'm kind of happy but I hate that I don't fully understand why it's necessary. Why doesn't it work unless I force the date to be a day we actually collected data on?

Any clarification gratefully received.