Attempting to get prior year spending data by month

Epicor ERP 10
1

What I am trying to do is break out spending by vendor and month/year, and then compare that spending to the same month last year. I though I could do this by simply getting my current spending in a subquery with a calculated field to find the prior year, and then join to another spending subquery using the prior year field. If I use datepart(), I get an invalid parameter error, and if I use month() and year() I get an invalid syntax near '(' error.

I’ve read through the thread below, but I haven’t been able to fix it, even by starting the query from scratch. Perhaps it’s an issue with my group bys?

MonthlySpending.baq (41.3 KB)

2

Looks like it should be ‘datepart(year,field)’ etc.

image

3

also, for your prior year, use the already calculated field. It doesn’t like that math in there for some reason.

image

1 Like
4

If you do those two things it should work.

image
image.png1117×214 17.6 KB

5

That fixed it…it’s weird, because I used datepart() in the BAQ and it gave me the parameter error, can they get corrupted? It had more stuff going on, the BAQ I sent you is a pared-down version.

6

The parameter error happened with the -1. that you did for the prior year. Not in the single fields.

2 Likes
7

And actually, I’m wondering if you naming your calculated field “year” has something to do with the errors.

It’s not specifically reserved in SQL, but it is in ODBC, and looks like it’s recommended to not use it.

2 Likes