Year on Year Number using Custom Expresssion

Get Help
1

I am trying to find ways to build custom expression to calculate year on year change in data
two ways this can go :
1.) Current Year month total (Nov 1 - Nov 18th 2024) compared to Last Year month total (Nov 1 - Nov 18th 2023)
2.) Current Year Year total (Jan 1 - Nov 18th 2024) compared to Last Year Year total (Jan 1 - Nov 18th 2023)

Method 1 ^ while trying to get last year month total - I get an error
CountIf([timestamp] >= datetimeSubtract(relativeDateTime(0, "month"), 1, "year")
AND [timestamp] < datetimeSubtract(relativeDateTime(1, "day"), 1, "year"))

Current year month total is working fine - CountIf([timestamp] >= relativeDateTime(0, "month") AND [timestamp < relativeDateTime(1, "day"))

Any tips or work around to get this easily - perhaps by trend vizualization

2

If you're using 50 or 51 you should be able to use the new offset function, have you tried that?

3

I don't think we are using that latest version - offset shows to be a unknown function when I try a custom expression as this
(
CountIf([timestamp] > offset(relativeDateTime(-30, "day"), 0, "year")
AND [timestamp] <= offset(relativeDateTime(0, "day"), 0, "year"))

CountIf([timestamp] > offset(relativeDateTime(-30, "day"), -1, "year")
AND [timestamp] <= offset(relativeDateTime(0, "day"), -1, "year"))
)
/
CountIf([timestamp] > offset(relativeDateTime(-30, "day"), -1, "year")
AND [timestamp] <= offset(relativeDateTime(0, "day"), -1, "year"))

Essentially I wish to look back recent 30 days and compare it to the same time period last year

4

what database engine and version are you using though?

if you don't have 50 or 51 then you'll have to do this via SQL

5

Yep. I gotchya , I'm still on V46. Thank you for helping