Does anybody know why I get an unexpected error when I try to perform a summary on this formula?

to_date({custcol4},'MM/DD/YYYY') - to_date(Lag({custcol4}, 1) OVER(partition by{item} ORDER BY {custcol4} ASC),'MM/DD/YYYY')

What type of field is it

This is a formula numeric, custcol4 is a date field. So I am finding the days between sales order lines and I want to average grouping by item

The error message you are receiving may be due to several reasons. Here are some possibilities: One or more of the fields referenced in the formula may not exist or may have been renamed or deleted. The data type of the "custcol4" field or the result of the formula may not be compatible with the summary function you are using. There may be a syntax error in the formula, such as a missing or extra parenthesis or quotation mark. Verify that the data type of the "custcol4" field is a valid date format, and that the result of the formula is a numerical value. Check the syntax of the formula for any errors. You may want to try breaking the formula down into smaller parts and testing them individually before combining them into the final formula. Try using a different summary function to see if that resolves the error. For example, if you were using the "SUM" function, try using the "AVG" function instead.

Did chatGPT write that^^?

@NElliott sometimes he has better answers 😂

Just be careful with that it can be very very wrong in it's responses.

Yes I know but sometimes but sometimes it is useful

The formula works fine before adding any summary to the results, but then it doesn't want to sum/avg/min/max/group, when breaking it down, lag() seems to be the culprit but i don't know why that would be giving me an issue.

@Joseph did you try a date type field? Pretty sure that's why

yes i have tried the

LAG({custcol4}, 1) OVER(partition by{item} ORDER BY {custcol4} ASC)

as a formula date and i get the same issue

but why are you taking away the to_date part?

I have both sections of the formula separated out and then the math in different columns. they all work and give the correct results when not summarized. as soon as i try anyt summary on either of the lines with LAG() i get an error.

Try Max() on the lag?

breaks it. goes from not loading the search with an unexpected error to invalid expression. But that was the direction I was thinking